Excel vba скопировать только видимые ячейки

Обновлено: 04.07.2024

Сама команда представляет собой выпадающее меню:

Команда Копировать является командой по умолчанию и копирует выделенные на активном листе ячейки. Т.е. по нажатии на значок без раскрытия меню будут скопированы выделенные на активном листе ячейки.
При нажатии на стрелочку справа от команды Копировать раскрывается меню, которое предоставляет доступ к командам:

Вставить все
Будут вставлены ячейки полностью - с форматами, формулами, примечаниями и т.п.
Вставить значения
Будут вставлены только значения скопированных ячеек. Формулы, форматы, примечания и т.п. перенесены не будут.
Вставить значения и форматы
Будут вставлены только значения и форматы скопированных ячеек. Формулы и примечания перенесены не будут. Может пригодиться если форматы ячеек сохранить надо, а формулы нет.
Вставить формулы
Будут вставлены только формулы скопированных ячеек. Если в какой-то из ячеек нет формулы - будет скопировано значение ячейки. Форматы, примечания и т.п. перенесены не будут. После вызова команды появится окно:

Вставка данных всегда производится начиная с активной ячейки. Вставка данной командой может быть произведена только если ранее ячейки были скопированы командой Копировать надстройки MulTEx.

Наиболее быстрые методы вставки - это вставка Вставить значения или Вставить формулы. В этом режиме вставка происходит примерно в 4-5 раз быстрее, чем Вставить все или Вставить значения и форматы.
Во время вставки в статус баре Excel(в нижней левой части окна Excel) отображается информация о текущем процессе вставки, чтобы можно было определить насколько быстро движется процесс и сколько примерно еще осталось:

Для чего это может быть нужно: если данные с одного листа необходимо перенести на другой лист, а данные на листах отфильтрованы - штатными средствами Excel простыми действиями копирования/вставки сделать этого не получится.

Пожалуйста, смотрите мой код ниже:

4 ответа

Нечто подобное .Value2 = .Value не работает на специальных видимых ячейках типа, потому что…

. например если lastRow = 50 и есть hiddenRows = 10 , тогда .

ваш источник Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible)
имеет lastRow - hiddenRows = 40 рядов
но ваш пункт назначения Range("A1:A" & lastRow).Value2
имеет lastRow = 50 строк.

На первом вы вычитаете видимые строки, чтобы они различались по размеру. Поэтому .Value2 = .Value не работает, потому что вы не можете заполнить 50 строк только 40 исходными строками.

Но вы можете сделать Copy и SpecialPaste

Тем не менее, я рекомендую избегать ActiveSheet или ActiveWorkbook , если это возможно, и ссылаться на рабочую книгу, например, ThisWorkbook . Мое предложение:

Чтобы определить, является ли ячейка видимой или нет, должны быть видны как ее столбец, так и строка. Это означает, что для свойства .Hidden столбца и строки должно быть установлено значение False .

Вот пример кода того, как копировать только видимые диапазоны между двумя листами.

Представьте, что у вас есть такой вклад в Worksheets (1):

Затем вы вручную скрываете столбец B и хотите вставить Worksheets(2) в каждую ячейку из Range(A1:C4) , без столбцов в B . Нравится:

Для этого необходимо проверить каждую ячейку в диапазоне, видим ли ее столбец или строка или нет. Возможное решение это:

Просто общий совет - всякий раз, когда вы используете что-то вроде этого Range("A1").Value2 = Range("A1").Value2 , убедитесь, что оба они одинаковы, а не слева - Value2 , а справа - .Value . Это, вероятно, не принесет то, что вы ожидаете.

Вы не можете выполнить прямую передачу значения без циклического перемещения по областям коллекции SpecialCells (xlCellTypeVisible).

Я пытаюсь создать динамический макрос, который можно использовать во многих различных книгах для достижения следующих целей: я хотел бы, чтобы пользователь вводил диапазон, который они хотели бы скопировать. Этот диапазон будет отфильтрован. Затем я хотел бы, чтобы пользователь выбирал диапазон для вставки скопированных данных. Диапазон, в который они будут вставляться, также фильтруется (фильтры могут отличаться от фильтров, из которых были скопированы данные. ИДЕАЛЬНО пользователь выберет только верхнюю левую ячейку диапазона для вставки (вместо того, чтобы выбирать все).

Приведенный ниже код скопирует отфильтрованные данные (только видимые ячейки), как я хотел.

Вставка - это, конечно, сложная часть. Я обнаружил, что могу успешно "вставить" вручную следующим образом:

Предположим, что скопированный диапазон - A1: A10, а диапазон вставки - B10: B20.

Я могу ввести формулу «= A1» в ячейку B10 ---> скопировать ячейку B10 ----> выбрать нужный диапазон для вставки в ----> использовать «Alt;» ярлык ----> вставить.

Следующий код пытается автоматизировать эту логику в VBA:

Это создает две проблемы:

Он правильно вставляется только в видимые ячейки, но в настоящее время вводит «= CopyRange» в качестве текста в диапазон, в который я хочу вставить (вместо формулы, устанавливающей «вставить ячейку» равной «копируемой ячейке».

Этот код еще не позволяет пользователю выбирать точный диапазон. Это позволяет им выбрать начальную точку, но затем копирует и вставляет в конец вставляемого столбца. Мне нужно, чтобы пользователь мог выбрать диапазон и еще не нашел способ сделать это без возникновения ошибок.

Поискав в сети, я нашел другие варианты «вставки макросов в видимые ячейки». Я попытался объединить их с первым фрагментом кода, которым поделился в этом посте. Эта комбинация показана ниже.

Это выполняется без ошибок, но макрос вставляется только до тех пор, пока не попадет в скрытую строку. Таким образом, если строки 1, 2, 3 и 6 видны, но 4 и 5 скрыты, макрос будет вставлен в 1, 2 и 3, но не на 4,5 или 6.

Я предпринял несколько других попыток, но пока они кажутся наиболее многообещающими. Мы очень ценим любые предложения / помощь, которые может предложить каждый. Самый важный ключ - сделать это полностью динамичным и максимально интуитивно понятным для пользователя.

Посмотрите мой код ниже:

просто чтобы бросить альтернативную версию:

который может иметь недостаток максимальной Address() максимальной) емкости Address()

Что-то вроде .Value2 =.Value не работает на специальных ячейках типа, видимых, потому что.

. например, если lastRow = 50 и там hiddenRows = 10 то.

На первом вы вычитаете видимые строки, поэтому они различаются по размеру. Поэтому .Value2 =.Value не работает, потому что вы не можете заполнить 50 строк только 40 исходными строками.

Но что вы можете сделать, это Copy and SpecialPaste

Тем не менее, я рекомендую, чтобы избежать ActiveSheet или ActiveWorkbook , если это возможно, и ссылаться на книгу, например, путем ThisWorkbook . Мое предложение:

Вы не можете выполнять прямую передачу стоимости без циклирования, хотя области коллекции SpecialCells (xlCellTypeVisible).

Иногда легче копировать все и избавляться от того, чего вы не хотите.

Чтобы определить, является ли ячейка видимой или нет, ее столбец и строка должны быть видимыми. Это означает, что для свойства .Hidden столбца и строки должно быть установлено значение False .

Вот пример кода для копирования только видимых диапазонов между двумя рабочими листами.

Представьте, что у вас есть такой ввод в Рабочем листе (1):

Затем вы вручную скроете столбец B и вы хотите попасть в Worksheets(2) каждую ячейку из Range(A1:C4) , без столбцов B Как это:

Чтобы сделать это, вы должны проверить каждую ячейку диапазона, независимо от того, является ли ее столбец или строка видимой или нет. Возможным решением является следующее:

Только общий совет - всякий раз, когда вы используете что-то вроде этого Range("A1").Value2 = Range("A1").Value2 убедитесь, что оба они одинаковы, а не левое значение Value2 а правое - .Value . Вероятно, это не принесет того, чего вы ожидаете.

Читайте также: