Vba excel добавить строку в range

Обновлено: 01.07.2022

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

Как вы знаете, VBA используется для записи и запуска макросов и автоматизации задач Excel, а также для более быстрого и точного выполнения повторяющихся задач.

В контексте рабочего листа Excel объект диапазона VBA обозначает ячейки, как одиночные, так и множественные. Объект диапазона может включать одну ячейку, всю строку или столбец или несколько ячеек, распределенных по строкам и столбцам.

Чтобы VBA запускал макросы и выполнял задачи, ему необходимо определить ячейки, в которых должны выполняться вызываемые задачи. Именно здесь концепция Range Objects находит свое применение.

Как использовать объект Range?

Чтобы ссылаться на объекты в VBA, мы используем иерархическую технику. Есть 3 иерархии:

  • Квалификатор объекта: Он относится к местоположению объекта, например, где он находится, т. Е. К книге или листу, на которые имеется ссылка.
  • Два других используются для манипулирования значениями ячеек. Это свойство и методы.
  • Свойство: Здесь хранится информация об объекте.
  • Метод: Это относится к действию, которое объект будет выполнять.

Например, для Range методом будут такие действия, как сортировка, форматирование, выбор, очистка и т. Д.

Это структура, которой следуют всякий раз, когда упоминается объект VBA. Эти 3 разделены точкой (.)

Application.Workbooks.Worksheets.Range

синтаксис

Application.Workbooks («Booknew.xlsm»). Рабочие листы («Sheet3»). Range («B1»)

Примеры

Пример №1 — Обращение к отдельной ячейке

Предположим, нам нужно выбрать ячейку «B2» в «sheet1» в книге.

Выполните следующие шаги:

  1. Откройте Excel. Откройте файл с расширением Excel «.xlsm», что означает «Книга с поддержкой макросов Excel». Книга Excel с типами «.xlsx» не позволит вам сохранить макросы, которые вы будете писать сейчас.
  2. Теперь, когда вы открыли книгу, вам нужно перейти в редактор VBA. Вы можете использовать сочетание клавиш «ALT + F11», чтобы открыть редактор, или воспользуйтесь приведенным ниже методом, как показано на снимке экрана:

Вы увидите экран, похожий на показанный ниже:

Теперь напишите код, как показано на скриншоте ниже.

Смотрите на скриншоте Excel ниже, что в настоящее время активирована ячейка A2. После запуска кода обратите внимание, где находится активированная ячейка.

Запустите код, как показано на скриншоте ниже:

Совет: вы также можете использовать горячую клавишу Excel, например F5, для запуска кода.

Вы увидите, что ячейка «B2» выбрана после выполнения программы.

Здесь вы даете инструкции программе перейти к определенной ячейке на определенном листе конкретной книги и выполнить действие, указанное здесь, для выбора.

Точно так же вы можете использовать синтаксис для выбора широкого спектра ячеек и диапазонов, а также выполнять с ними различные действия.

Например, здесь, чтобы выбрать вторую строку. Запустите приведенный ниже код, чтобы выбрать всю строку

Здесь диапазон («2: 2») означает вторую строку. Вы можете вернуться к своему листу Excel и увидеть результаты, как показано на скриншоте ниже.

Например, здесь, чтобы выбрать весь столбец C. Запустите приведенный ниже код и просмотрите результаты.

После ввода приведенного выше кода вы увидите, что весь столбец выбран на вашем листе Excel. См. Снимок экрана ниже.

Здесь диапазон («C: C») означает столбец C.

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

Просто внесите следующие изменения в часть диапазона, показанную в коде.

Пример №4 — Выбор смежных ячеек: диапазон («B2: D6»)

Пример № 5 — Выбор несмежных ячеек: диапазон («B1: C5, G1: G3»)

Пример №6 — Выбор пересечения диапазона: Диапазон («B1: G5 G1: G3»)

[Note the absence of comma here]. Здесь вы увидите, что выбираются от G1 до G3, которые являются общими ячейками в указанном диапазоне.

Теперь следующим примером будет выбор группы ячеек на листе и объединение их в одну ячейку.

Предположим, вы хотите объединить ячейки «B1: C5» в одну. См. Приведенный ниже код и следуйте инструкциям.

Здесь «.merge» — это действие, которое мы выполняем над группой ячеек, заданной в диапазоне.

Предположим, что ячейки «F2: H6» выделены желтым, и мы хотим очистить это форматирование Excel. Другой сценарий, возможно, вы хотите удалить все форматирование либо на всем листе, либо в группе ячеек.

Смотрите скриншоты ниже, чтобы продолжить. Сначала я покажу вам отформатированные ячейки (F2: H6).

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

Синтаксис: ThisWorkbook.Worksheets («Sheet1»). Range («F2: H6»). ClearFormats

Вы можете обратиться к этому снимку экрана, приведенному ниже:

Точно так же вы можете очистить содержимое диапазона ячеек с помощью действия «.ClearContents».

Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.

Синтаксис

Параметры

Параметры Описание
Destination Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена.

Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.

Метод Range.Copy

Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.

Синтаксис

Параметры

Параметры Описание
Destination Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена.

Метод Worksheet.Paste

Синтаксис

Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.

Параметры

Параметры Описание
Destination Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект.
Link Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию).

В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.

Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).

Примеры

Вырезание и вставка диапазона одной строкой (перемещение):

Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:

Копирование и вставка диапазона одной строкой:

Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:

Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:

18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”

Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2

а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:

Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )

Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?

Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :

Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.

(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)

Range.Insert – это метод, который вставляет диапазон пустых ячеек (в том числе одну ячейку) на рабочий лист Excel в указанное место, сдвигая существующие в этом месте ячейки вправо или вниз. Если в буфере обмена содержится объект Range, то вставлен будет он со своими значениями и форматами.

Синтаксис

Expression – выражение (переменная), возвращающее объект Range.

Параметры

Параметр Описание Значения
Shift Необязательный параметр. Определяет направление сдвига ячеек. Если параметр Shift опущен, направление выбирается в зависимости от формы* диапазона. xlShiftDown (-4121) – ячейки сдвигаются вниз;
xlShiftToRight (-4161) – ячейки сдвигаются вправо.
CopyOrigin Необязательный параметр. Определяет: из каких ячеек копировать формат. По умолчанию формат копируется из ячеек сверху или слева. xlFormatFromLeftOrAbove (0) – формат копируется из ячеек сверху или слева;
xlFormatFromRightOrBelow (1) – формат копируется из ячеек снизу или справа.

* Если диапазон горизонтальный или квадратный (количество строк меньше или равно количеству столбцов), ячейки сдвигаются вниз. Если диапазон вертикальный (количество строк больше количества столбцов), ячейки сдвигаются вправо.

Примеры

Простая вставка диапазона

Вставка диапазона ячеек в диапазон «F5:K9» со сдвигом исходных ячеек вправо:

Если бы параметр Shift не был указан, сдвиг ячеек, по умолчанию, произошел бы вниз, так как диапазон горизонтальный.

Вставка вырезанного диапазона

Вставка диапазона, вырезанного в буфер обмена методом Range.Cut, из буфера обмена со сдвигом ячеек по умолчанию:

Обратите внимание, что при использовании метода Range.Cut, точка вставки (в примере: Range("D2") ) не может находится внутри вырезанного диапазона, а также в строке или столбце левой верхней ячейки вырезанного диапазона вне вырезанного диапазона (в примере: строка 1 и столбец «A»).

Вставка скопированного диапазона

Вставка диапазона, скопированного в буфер обмена методом Range.Copy, из буфера обмена со сдвигом ячеек по умолчанию:

Обратите внимание, что при использовании метода Range.Copy, точка вставки (в примере: Range("F2") ) не может находится внутри скопированного диапазона, но в строке или столбце левой верхней ячейки скопированного диапазона вне скопированного диапазона находится может.

Вставка и перемещение строк

Вставка одной строки на место пятой строки со сдвигом исходной строки вниз:

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

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

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

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

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.

Примечания

Элемент по умолчанию объекта Range направляет вызовы без параметров в свойство Value, а вызовы с параметрами — в элемент Item. Таким образом, someRange = someOtherRange соответствует someRange.Value = someOtherRange.Value , someRange(1) соответствует someRange.Item(1) и someRange(1,1) соответствует someRange.Item(1,1) .

В разделе Пример описаны следующие свойства и методы для возврата объекта Range:

Пример

Чтобы вернуть объект Range, представляющий одну ячейку или диапазон ячеек, используйте синтаксис Range ( arg ), где arg обозначает диапазон. В следующем примере значение ячейки A1 помещается в ячейку A5.

В следующем примере диапазон A1:H8 заполняется случайными числами путем задания формулы для каждой ячейки в диапазоне. При использовании без квалификатора объекта (объекта слева от точки) свойство Range возвращает диапазон на активном листе. Если активное окно не является листом, метод завершается с ошибкой.

Используйте метод Activate объекта Worksheet, чтобы активировать лист перед использованием свойства Range без явного квалификатора объекта.

В следующем примере очищается содержимое диапазона Criteria.

Если используется текстовый аргумент для адреса диапазона, необходимо указать адрес в нотации стиля A1 (нельзя использовать нотацию в стиле R1C1).

Чтобы получить диапазон, содержащий все отдельные ячейки листа, используйте свойство Cells на листе. Вы можете обращаться к отдельным ячейкам, используя синтаксис Item(строка, столбец), где строка — индекс строки, а столбец — индекс столбца. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги ячейке A1 присваивается значение 24, а в ячейке B1 — значение 42.

В следующем примере задается формула для ячейки A2.

Хотя также можно использовать Range("A1") , чтобы вернуть значение ячейки A1, иногда свойство Cells может быть удобнее, так как позволяет использовать переменную для строки или столбца. В следующем примере создаются заголовки столбцов и строк на листе Sheet1. Обратите внимание, что после активации листа можно использовать свойство Cells без явного объявления листа (оно возвращает ячейку на активном листе).

Хотя для изменения ссылок в стиле A1 можно использовать строковые функции Visual Basic, проще (и лучше при программировании) использовать нотацию Cells(1, 1) .

Используйте синтаксис_выражение_.Cells, где выражение возвращает объект Range, чтобы получить диапазон с тем же адресом, состоящий из отдельных ячеек. В таком диапазоне отдельные ячейки доступны с помощью синтаксиса Item(строка, столбец) относительно левого верхнего угла первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги в ячейках C5 и D5 указывается формула.

Чтобы вернуть объект Range, используйте синтаксис Range ( ячейка1, ячейка2 ), где ячейка1 и ячейка2 — это объекты Range, указывающие начальную и конечную ячейки. В следующем примере устанавливается тип линии границы для ячеек A1:J10.

Имейте в виду, что точка перед каждым появлением свойства Cells является обязательной, если результат предыдущего оператора With нужно применять к свойству Cells. В данном случае указано, что ячейки расположены на листе один (без точки свойство Cells будет возвращать ячейки активного листа).

Чтобы получить диапазон, содержащий все строки листа, используйте свойство Rows на листе. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это индекс строки. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.

В следующем примере удаляются строки 4 и 10 первого листа активной книги.

Чтобы получить диапазон, содержащий все столбцы листа, используйте свойство Columns на листе. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это индекс столбца в виде числа или адреса столбца в формате А1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.

В следующем примере удаляются столбцы B, C, E и J первого листа активной книги.

Используйте синтаксис_выражение_.Rows, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из строк первой области диапазона. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это относительный индекс строки от верхнего края первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.

В следующем примере удаляются диапазоны C8:D8 и C6:D6 первого листа активной книги.

Используйте синтаксис_выражение_.Columns, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из столбцов первой области диапазона. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это относительный индекс столбца от левого края первой области диапазона, указанный в виде числа или адреса столбца в формате A1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.

В следующем примере удаляются диапазоны L2:L10, G2:G10, F2:F10 и D2:D10 первого листа активной книги.

Чтобы вернуть диапазон с указанным смещением относительно другого диапазона, используйте синтаксис Offset ( строка, столбец ), где строка и столбец — это смещения строк и столбцов. В следующем примере выделяются ячейки, расположенные на три строки вниз и на один столбец вправо от ячейки в левом верхнем углу текущего выделенного фрагмента. Нельзя выбрать ячейку, которая находится не на активном листе, поэтому сначала необходимо активировать лист.

Используйте синтаксис Union ( диапазон1, диапазон2, . ) для возврата диапазонов из нескольких областей, то есть диапазонов, состоящих из двух или более смежных блоков ячеек. В следующем примере создается объект, определенный как объединение диапазонов A1:B2 и C3:D4, а затем выбирается определенный диапазон.

При работе с выделенными фрагментами, содержащими несколько областей, удобно применять свойство Areas. Оно разделяет выделенный фрагмент с несколькими областями на отдельные объекты Range, а затем возвращает объекты в виде коллекции. Используйте свойство Count в возвращенной коллекции, чтобы убедиться, что выделение содержит более одной области, как показано в следующем примере.

В этом примере используется метод AdvancedFilter объекта Range для создания списка уникальных значений, а также количества появлений этих уникальных значений в диапазоне столбца A.

Методы

Свойства

См. также

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

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