Работа с ячейками и диапазонами в Excel VBA (выделение, копирование, перемещение, редактирование)
При работе с Excel большую часть времени вы проводите в области рабочего листа, работая с ячейками и диапазонами.
А если вы хотите автоматизировать свою работу в Excel с помощью VBA, вам нужно знать, как работать с ячейками и диапазонами с помощью VBA.
С диапазонами в VBA можно делать много разных вещей (например, выбирать, копировать, перемещать, редактировать и т. Д.).
Чтобы охватить эту тему, я разделю это руководство на разделы и покажу вам, как работать с ячейками и диапазонами в Excel VBA на примерах.
Все коды, которые я упоминаю в этом руководстве, необходимо поместить в редактор VB. Перейдите в раздел «Где разместить код VBA», чтобы узнать, как это работает.
Если вы заинтересованы в изучении VBA простым способом, ознакомьтесь с моими Онлайн-обучение по Excel VBA.
Выбор ячейки / диапазона в Excel с помощью VBA
Чтобы работать с ячейками и диапазонами в Excel с помощью VBA, вам не нужно выбирать его.
В большинстве случаев лучше не выбирать ячейки или диапазоны (как мы увидим).
Несмотря на это, важно, чтобы вы прошли через этот раздел и поняли, как он работает. Это будет иметь решающее значение в вашем обучении VBA, и многие концепции, описанные здесь, будут использоваться в этом руководстве.
Выбор отдельной ячейки с помощью VBA
Если вы хотите выбрать одну ячейку на активном листе (скажем, A1), вы можете использовать приведенный ниже код:
В приведенном выше коде есть обязательные части «Sub» и «End Sub», а также строка кода, которая выбирает ячейку A1.
Диапазон («A1») сообщает VBA адрес ячейки, на которую мы хотим сослаться.
Выбирать является методом объекта Range и выбирает ячейки / диапазон, указанные в объекте Range. Ссылки на ячейки необходимо заключать в двойные кавычки.
Этот код покажет ошибку, если лист диаграммы является активным. Лист диаграммы содержит диаграммы и не используется широко. Поскольку в нем нет ячеек / диапазонов, приведенный выше код не может выбрать его, и в результате будет отображаться ошибка.
Обратите внимание: поскольку вы хотите выбрать ячейку на активном листе, вам просто нужно указать адрес ячейки.
Но если вы хотите выделить ячейку на другом листе (скажем, Sheet2), вам нужно сначала активировать Sheet2, а затем выбрать ячейку в нем.
Точно так же вы также можете активировать книгу, затем активировать в ней определенный рабочий лист, а затем выбрать ячейку.
Обратите внимание, что при обращении к книгам необходимо использовать полное имя вместе с расширением файла (.xlsx в приведенном выше коде). Если книга никогда не сохранялась, вам не нужно использовать расширение файла.
Эти примеры не очень полезны, но позже в этом руководстве вы увидите, как мы можем использовать те же концепции для копирования и вставки ячеек в Excel (с использованием VBA).
Так же, как мы выбираем ячейку, мы также можем выбрать диапазон.
В случае диапазона это может быть диапазон фиксированного или переменного размера.
В диапазоне фиксированного размера вы будете знать, насколько велик этот диапазон, и можете использовать точный размер в своем коде VBA. Но с диапазоном переменного размера вы не представляете, насколько велик диапазон, и вам нужно использовать немного магии VBA.
Выбор диапазона фиксированного размера
Другой способ сделать это — использовать приведенный ниже код:
Приведенный выше код берет адрес верхней левой ячейки (A1) и адрес нижней правой ячейки (D20) и выбирает весь диапазон. Этот метод становится полезным, когда вы работаете с диапазонами переменного размера (как мы увидим, когда позже в этом руководстве будет рассмотрено свойство End).
Если вы хотите, чтобы выбор происходил в другой книге или на другом листе, вам необходимо сообщить VBA точные имена этих объектов.
Например, приведенный ниже код выберет диапазон A1: D20 на листе Sheet2 в книге Book2.
А что, если вы не знаете, сколько там строк. Что, если вы хотите выбрать все ячейки, в которых есть значение.
В этих случаях вам необходимо использовать методы, показанные в следующем разделе (при выборе диапазона переменного размера).
Выбор диапазона переменного размера
Вы можете выбрать диапазон ячеек разными способами. Выбранный вами метод будет зависеть от того, как структурированы данные.
В этом разделе я расскажу о некоторых полезных методах, которые действительно полезны при работе с диапазонами в VBA.
Выберите с помощью свойства CurrentRange
В случаях, когда вы не знаете, сколько строк / столбцов содержат данные, вы можете использовать свойство CurrentRange объекта Range.
Свойство CurrentRange охватывает все смежные заполненные ячейки в диапазоне данных.
Ниже приведен код, который выберет текущий регион, содержащий ячейку A1.
Вышеупомянутый метод хорош, когда у вас есть все данные в виде таблицы без пустых строк / столбцов в ней.
Но если у вас есть пустые строки / столбцы в ваших данных, он не будет выбирать те, которые находятся после пустых строк / столбцов. На изображении ниже код CurrentRegion выбирает данные до строки 10, поскольку строка 11 пуста.
В таких случаях вы можете использовать свойство UsedRange объекта Worksheet.
Выберите с помощью свойства UsedRange
UsedRange позволяет ссылаться на любые измененные ячейки.
Таким образом, приведенный ниже код выберет все используемые ячейки на активном листе.
Обратите внимание, что если у вас есть удаленная ячейка, которая использовалась, она будет учитываться приведенным выше кодом, и все ячейки до тех пор, пока эта используемая ячейка не будет выбрана.
Выберите с помощью свойства конца
Свойство End позволяет выбрать последнюю заполненную ячейку. Это позволяет имитировать действие клавиш управления стрелками вниз / вверх или клавиш управления вправо / влево.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро выбрать последние заполненные ячейки в столбце A.
Проблема здесь в том, что данные могут меняться, и вы не знаете, сколько ячеек заполнено. Если вам нужно сделать это с помощью клавиатуры, вы можете выбрать ячейку A1, а затем использовать клавиши Control + стрелка вниз, и она выберет последнюю заполненную ячейку в столбце.
Теперь давайте посмотрим, как это сделать с помощью VBA. Этот метод пригодится, когда вы хотите быстро перейти к последней заполненной ячейке в столбце переменного размера.
Приведенный выше код перейдет к последней заполненной ячейке в столбце A.
Точно так же вы можете использовать End (xlToRight) для перехода к последней заполненной ячейке в строке.
Теперь, что, если вы хотите выбрать весь столбец вместо перехода к последней заполненной ячейке.
Вы можете сделать это, используя приведенный ниже код:
В приведенном выше коде мы использовали первую и последнюю ссылку на ячейку, которую нам нужно выбрать. Независимо от количества заполненных ячеек, приведенный выше код выберет все.
Вспомните приведенный выше пример, в котором мы выбрали диапазон A1: D20, используя следующую строку кода:
Здесь A1 — это верхняя левая ячейка, а D20 — нижняя правая ячейка в диапазоне. Мы можем использовать ту же логику при выборе диапазонов переменного размера. Но поскольку мы не знаем точного адреса нижней правой ячейки, мы использовали свойство End, чтобы получить его.
В Range («A1», Range («A1»). End (xlDown)) «A1» относится к первой ячейке, а Range («A1»). End (xlDown) относится к последней ячейке. Поскольку мы предоставили обе ссылки, метод Select выбирает все ячейки между этими двумя ссылками.
Точно так же вы также можете выбрать весь набор данных, состоящий из нескольких строк и столбцов.
Приведенный ниже код выберет все заполненные строки / столбцы, начиная с ячейки A1.
Разница между использованием CurrentRegion и End
Если вам интересно, зачем использовать свойство End для выбора заполненного диапазона, когда у нас есть свойство CurrentRegion, позвольте мне рассказать вам разницу.
С помощью свойства End вы можете указать начальную ячейку. Например, если у вас есть данные в формате A1: D20, но первая строка — это заголовки, вы можете использовать свойство End для выбора данных без заголовков (используя приведенный ниже код).
Но CurrentRegion автоматически выберет весь набор данных, включая заголовки.
До сих пор в этом руководстве мы видели, как по-разному ссылаться на диапазон ячеек.
Теперь давайте посмотрим, как мы можем использовать эти методы для выполнения некоторой работы.
Копирование ячеек / диапазонов с помощью VBA
Как я упоминал в начале этого урока, выбор ячейки не является необходимым для выполнения действий с ней. В этом разделе вы увидите, как копировать ячейки и диапазоны, даже не выбирая их.
Копирование одной ячейки
Если вы хотите скопировать ячейку A1 и вставить ее в ячейку D1, приведенный ниже код сделает это.
Обратите внимание, что метод копирования объекта диапазона копирует ячейку (точно так же, как Control + C) и вставляет ее в указанное место назначения.
В приведенном выше примере кода место назначения указано в той же строке, где вы используете метод копирования. Если вы хотите сделать свой код еще более читаемым, вы можете использовать приведенный ниже код:
Приведенные выше коды скопируют и вставят значение, а также форматирование / формулы в него.
Как вы уже могли заметить, приведенный выше код копирует ячейку, не выбирая ее. Независимо от того, где вы находитесь на листе, код скопирует ячейку A1 и вставит ее в D1.
Также обратите внимание, что приведенный выше код перезапишет любой существующий код в ячейке D2. Если вы хотите, чтобы Excel сообщал вам, есть ли что-то в ячейке D1, не перезаписывая это, вы можете использовать приведенный ниже код.
Копирование диапазона фиксированного размера
Если вы хотите скопировать A1: D20 в J1: M20, вы можете использовать следующий код:
В ячейке назначения вам просто нужно указать адрес верхней левой ячейки. Код автоматически скопирует точный скопированный диапазон в место назначения.
Вы можете использовать ту же конструкцию для копирования данных с одного листа на другой.
Приведенный ниже код скопирует A1: D20 с активного листа на Sheet2.
Вышеупомянутое копирует данные с активного листа. Поэтому перед запуском кода убедитесь, что лист с данными является активным. На всякий случай вы также можете указать имя рабочего листа при копировании данных.
Преимущество приведенного выше кода заключается в том, что независимо от того, какой лист активен, он всегда копирует данные из Sheet1 и вставляет их в Sheet2.
Вы также можете скопировать именованный диапазон, используя его имя вместо ссылки.
Например, если у вас есть именованный диапазон под названием «SalesData», вы можете использовать приведенный ниже код, чтобы скопировать эти данные в Sheet2.
Если область действия именованного диапазона — это вся книга, вам не нужно находиться на листе с именованным диапазоном для запуска этого кода. Поскольку именованный диапазон ограничен рабочей книгой, вы можете получить к нему доступ с любого листа, используя этот код.
Если у вас есть таблица с именем Table1, вы можете использовать приведенный ниже код, чтобы скопировать ее на Sheet2.
Вы также можете скопировать диапазон в другую рабочую книгу.
В следующем примере я копирую таблицу Excel (Table1) в книгу Book2.
Этот код будет работать, только если рабочая книга уже открыта.
Копирование диапазона переменного размера
Один из способов скопировать диапазоны переменного размера — преобразовать их в именованные диапазоны или таблицу Excel и использовать коды, как показано в предыдущем разделе.
Но если вы не можете этого сделать, вы можете использовать свойство CurrentRegion или End объекта диапазона.
Приведенный ниже код скопирует текущую область на активном листе и вставит ее в Sheet2.
Если вы хотите скопировать первый столбец вашего набора данных до последней заполненной ячейки и вставить его в Sheet2, вы можете использовать следующий код:
Если вы хотите скопировать как строки, так и столбцы, вы можете использовать приведенный ниже код:
Обратите внимание, что все эти коды не выделяют ячейки при выполнении. В общем, вы найдете только несколько случаев, когда вам действительно нужно выбрать ячейку / диапазон, прежде чем работать с ним.
Назначение диапазонов объектным переменным
До сих пор мы использовали полный адрес ячеек (например, Workbooks («Book2.xlsx»). Worksheets («Sheet1»). Range («A1»)).
Чтобы сделать ваш код более управляемым, вы можете назначить эти диапазоны объектным переменным, а затем использовать эти переменные.
Например, в приведенном ниже коде я назначил диапазон источника и назначения переменным объекта, а затем использовал эти переменные для копирования данных из одного диапазона в другой.
Начнем с объявления переменных как объектов Range. Затем мы назначаем диапазон этим переменным с помощью оператора Set. После того, как диапазон был назначен переменной, вы можете просто использовать переменную.
Введите данные в следующую пустую ячейку (используя поле ввода)
Вы можете использовать поля ввода, чтобы пользователь мог вводить данные.
Например, предположим, что у вас есть набор данных ниже, и вы хотите ввести запись о продажах, вы можете использовать поле ввода в VBA. Используя код, мы можем убедиться, что он заполняет данные в следующей пустой строке.
Приведенный выше код использует поле ввода VBA для получения входных данных от пользователя, а затем вводит их в указанные ячейки.
Обратите внимание, что мы не использовали точные ссылки на ячейки. Вместо этого мы использовали свойства End и Offset, чтобы найти последнюю пустую ячейку и заполнить ее данными.
Этот код нельзя использовать. Например, если вы вводите текстовую строку, когда поле ввода запрашивает количество или сумму, вы заметите, что Excel позволяет это. Вы можете использовать условие If, чтобы проверить, является ли значение числовым или нет, и затем разрешить его соответственно.
Цикл по ячейкам / диапазонам
До сих пор мы видели, как выбирать, копировать и вводить данные в ячейки и диапазоны.
В этом разделе мы увидим, как перебирать набор ячеек / строк / столбцов в диапазоне. Это может быть полезно, когда вы хотите проанализировать каждую ячейку и выполнить какое-либо действие на ее основе.
Например, если вы хотите выделить каждую третью строку в выделенном фрагменте, вам нужно пройтись по ней и проверить номер строки. Точно так же, если вы хотите выделить все отрицательные ячейки, изменив цвет шрифта на красный, вам нужно пройти и проанализировать значение каждой ячейки.
Вот код, который будет перебирать строки в выбранных ячейках и выделять альтернативные строки.
В приведенном выше коде функция MOD используется для проверки номера строки в выделенном фрагменте. Если номер строки четный, он выделяется голубым цветом.
Вот еще один пример, в котором код проходит через каждую ячейку и выделяет ячейки с отрицательными значениями.
Обратите внимание, что вы можете сделать то же самое, используя условное форматирование (которое является динамическим и лучший способ сделать это). Этот пример предназначен только для того, чтобы показать вам, как цикл работает с ячейками и диапазонами в VBA.
Куда поместить код VBA
Хотите знать, где находится код VBA в вашей книге Excel?
В Excel есть серверная часть VBA, называемая редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.
Как вставить таблицу Excel в Word с помощью VBA — Технологии и программы
Перенос данных на другие листы книги Excel позволяет связывать несколько таблиц. Это удобно тем, что при замене какого-то значения на одном листе, меняются значения на всех остальных. При создании годовых отчетов это незаменимая вещь.
Как Скопировать Строки из Одной Таблицы в Другую Excel Vba
Шаг 235.
VBA в MSExcel. Оформление командировочных удостоверений. Вставка содержимого выбранной строки в конец списка
На этом шаге мы рассмотрим перечень окончательных действий по созданию указанного приложения .
Оставим за пользователем только выбор копируемой строки, а все остальное поручите выполнять Excel в автоматическом режиме при нажатии на созданную кнопку Копирование и вставка выделенной строки (рисунок 1), которой назначьте следующий записываемый макрос с именем ВставкаСтроки .
Рис.1. Левая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ
- перед записью макроса выделите выбранную строку, например, строку 9;
- выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена;
- в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно ранее определенным договоренностям — это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
- выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А . Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
- остановите запись макроса.
Назначьте макрос кнопке и выделив одну из заполненных строк списка журнала регистрации, нажатием на созданную кнопку, проверьте правильность выполнения заданной последовательности ваших действий.
Исследование кода VBA при записи макроса ВставкаСтроки
После записи макроса не откладывайте на потом исследование записанного кода VBA , а открыв Редактор Visual Basic просмотрите записанные действия и при необходимости отредактируйте код VBA . Механически сгенерированный код показан на рисунке 2.
Между ключевыми словами начала и окончания выполнения подпрограммы находится восемь строк кода. По очереди рассмотрим каждую строку. Первая строка:
- Selection — это неявная ссылка на свойство объекта Worksheet (рабочий лист) и означает выделенный (объектом выделения может быть ячейка, диапазон или несколько несмежных диапазонов ячеек, строка или столбец) диапазон.
- Copy — название метода и означает команду копировать выделенный объект.
Обратите на точку, находящемуся между текстом кода. Она называется операция точка , и предназначена для разделения названия свойства и названия метода в этой строке.
в которой Rows — это объект (строки), в скобках указано какие именно строки, а Select это метод (выделить). Согласно заданным условиям разрабатываемого приложения это должна быть 200-я строка, поэтому замените значение 14 на значение 200.
означает что операция вставки (Paste) выполняется на активном рабочем листе (ActiveSheet) . В данном случае ActiveSheet — свойство объекта Workbook и означает, что у этого объекта есть такой текущий объект, как активный рабочий лист.
Обратите внимание на три рассмотренные строки в совокупности. Они предназначены для выполнения одной операции — копирования выделенной строки с целью вставки ее содержимого в другую строку. Но ту же операцию может выполнить всего одна строка, в которой в качестве аргумента метода Copy указано место вставки содержимого скопированного диапазона:
- установите курсор в конце первой строки и введите открывающуюся скобку;
- нажмите клавишу Delete , и текст второй строки кода переместится в конец первой строки;
- введите закрывающуюся скобку после указания диапазона;
- с помощью клавиши Delete удалите дальнейший ненужный код.
При подобном редактировании кода макроса нет необходимости даже соблюдать интервал (пробелы) между операторами Copy и Rows . После окончания редактирования, установка интервалов между операторами при отсутствии ошибок выполняется Редактором Microsoft Visual Basic автоматически.
Excel генерирует автоматически. Она указывает на то, что прежде, чем выполнять сортировку, необходимо было указать, по какой области будет происходить сортировка. Удаление этой строки никак не повлияет на выполнение макроса.
Следующую строку Excel генерирует также самостоятельно:
и она носит в данном макросе больше информационный характер. Строка указывает что метод копирования (вырезания) CutCopyMode закончил свое действие и буфер обмена не содержит никаких данных, потому что ему присвоено значение False (Ложь) . Удаление этой строки не повлечет никаких изменений в выполнении записанного макроса.
Следующая выполняемая операция (сортировка) состоит из двух строк:
В одной строке Редактора может находиться до 1024 символов. Но если все 1024 символа разместить в одной строке, то это затруднит ее чтение. Для переноса кода строки в следующую, используется символ подчеркивания (_), перед которым обязательно должен находиться пробел.
Как видно из записанного кода для присвоения методам значений применяются знаки := (двоеточие и равно).
Полученный в результате редактирования код макроса показан на рисунке 3, а остальной текст кода можно удалить.
Рис.3. Отредактированный код VBA подпрограммы вставки в конец списка содержимого выделенной строки
За счет минимизации текста подпрограммы было увеличено его быстродействие. Ведь для Excel нужно было прочитать ранее 8 строк, по каждой из которых он должен был предпринять какие-то действия, что значительно дольше, чем 3.
Со следующего шага мы начнем рассматривать автоматизацию рабочего процесса по формированию и учету кассовых документов .
[expert_bq id=»1570″]Но ту же операцию может выполнить всего одна строка, в которой в качестве аргумента метода Copy указано место вставки содержимого скопированного диапазона. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Важное замечание! Макрос работает со следующим ограничением — объединяемые файлы не должны быть открыты физически или находиться в памяти, в буфере обмена. В таком случае вы получите ошибку во время выполнения.3 способа объединить несколько файлов Excel в один | Mister-Office
- перед записью макроса выделите выбранную строку, например, строку 9;
- выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена;
- в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно ранее определенным договоренностям — это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
- выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А . Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
- остановите запись макроса.
В зависимости от того, сколько файлов вы выбрали, дайте макросу несколько секунд или минут для их обработки. После завершения всех операций он сообщит вам, сколько файлов было обработано и сколько листов было объединено:
