Excel выберите диапазон vba excel

Обновлено: 03.07.2024

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

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

С диапазонами в VBA можно выполнять множество различных действий (например, выбирать, копировать, перемещать, редактировать и т. д.).

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

Приступим.

Все коды, которые я упоминаю в этом руководстве, необходимо поместить в редактор VB. Перейдите в раздел «Куда поместить код VBA», чтобы узнать, как это работает.

Если вы заинтересованы в простом изучении VBA, ознакомьтесь с моим онлайн-обучением Excel VBA.

Это руководство охватывает:

Выбор ячейки/диапазона в Excel с помощью VBA

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

В большинстве случаев лучше не выбирать ячейки или диапазоны (как мы увидим).

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

Начнем с очень простого примера.

Выбор отдельной ячейки с помощью VBA

Если вы хотите выбрать одну ячейку на активном листе (скажем, A1), вы можете использовать следующий код:

В приведенном выше коде есть обязательные части «Sub» и «End Sub», а также строка кода, которая выбирает ячейку A1.

Range("A1") сообщает VBA адрес ячейки, на которую мы хотим сослаться.

Select — это метод объекта Range, который выбирает ячейки/диапазон, указанные в объекте Range. Ссылки на ячейки должны быть заключены в двойные кавычки.

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

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

Но если вы хотите выбрать ячейку на другом листе (скажем, на Листе2), вам нужно сначала активировать Лист2, а затем выбрать ячейку на нем.

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

Обратите внимание, что при обращении к книгам необходимо использовать полное имя вместе с расширением файла (.xlsx в приведенном выше коде). Если книга никогда не сохранялась, вам не нужно использовать расширение файла.

Теперь эти примеры не очень полезны, но позже в этом руководстве вы увидите, как мы можем использовать те же концепции для копирования и вставки ячеек в Excel (с помощью VBA).

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

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

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

Давайте посмотрим, как это сделать.

Выбор диапазона размера исправления

Вот код, который выберет диапазон A1:D20.

Еще один способ сделать это — использовать приведенный ниже код:

Приведенный выше код берет адрес верхней левой ячейки (A1) и адрес нижней правой ячейки (D20) и выбирает весь диапазон. Этот метод становится полезным, когда вы работаете с диапазонами переменного размера (как мы увидим, когда свойство End будет рассмотрено позже в этом руководстве).

Если вы хотите, чтобы выбор происходил в другой книге или на другом листе, вам нужно сообщить VBA точные имена этих объектов.

Например, приведенный ниже код выберет диапазон A1:D20 на листе Sheet2 в книге Book2.

Что делать, если вы не знаете, сколько там строк. Что делать, если вы хотите выбрать все ячейки, в которых есть значение.

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

Выбор диапазона переменного размера

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

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

Выбрать с помощью свойства CurrentRange

Если вы не знаете, сколько строк/столбцов содержит данные, вы можете использовать свойство CurrentRange объекта Range.

Свойство CurrentRange охватывает все смежные заполненные ячейки в диапазоне данных.

Ниже приведен код, который выберет текущую область, содержащую ячейку A1.

Описанный выше метод удобен, когда все данные представлены в виде таблицы без пустых строк/столбцов.

Но если в ваших данных есть пустые строки/столбцы, он не будет выбирать строки после пустых строк/столбцов. На изображении ниже код CurrentRegion выбирает данные до строки 10, поскольку строка 11 пуста.

В таких случаях вы можете использовать свойство UsedRange объекта Worksheet.

Выбрать с помощью свойства UsedRange

UsedRange позволяет ссылаться на любые измененные ячейки.

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

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

Выберите с помощью свойства End

Теперь эта часть действительно полезна.

Свойство End позволяет выбрать последнюю заполненную ячейку. Это позволяет вам имитировать эффект клавиш управления со стрелками вниз/вверх или клавиш управления вправо/влево.

Давайте попробуем понять это на примере.

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

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

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

Приведенный выше код перейдет к последней заполненной ячейке в столбце A.

Аналогичным образом вы можете использовать End(xlToRight) для перехода к последней заполненной ячейке в строке.

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

Это можно сделать с помощью приведенного ниже кода:

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

Вспомните приведенный выше пример, в котором мы выбрали диапазон A1:D20, используя следующую строку кода:

Здесь A1 — верхняя левая ячейка, а D20 — нижняя правая ячейка диапазона. Мы можем использовать ту же логику при выборе диапазонов переменного размера. Но поскольку мы не знаем точного адреса нижней правой ячейки, мы использовали свойство End, чтобы получить его.

В Range("A1", Range("A1").End(xlDown)), "A1" относится к первой ячейке, а Range("A1").End(xlDown) относится к последней ячейке. Поскольку мы предоставили обе ссылки, метод Select выбирает все ячейки между этими двумя ссылками.

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

Приведенный ниже код выберет все заполненные строки/столбцы, начиная с ячейки A1.

В приведенном выше коде мы использовали Range("A1").End(xlDown).End(xlToRight), чтобы получить ссылку на нижнюю правую заполненную ячейку набора данных.

Разница между использованием 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 с активного листа на Лист2.

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

Приведенный выше код хорош тем, что независимо от того, какой лист активен, он всегда копирует данные с Листа1 и вставляет их на Лист2.

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

Например, если у вас есть именованный диапазон под названием "Данные о продажах", вы можете использовать приведенный ниже код, чтобы скопировать эти данные на Лист2.

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

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

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

В следующем примере я копирую таблицу Excel (Table1) в книгу Book2.

Этот код будет работать, только если рабочая книга уже открыта.

Копирование диапазона переменного размера

Один из способов копирования диапазонов переменного размера — преобразовать их в именованные диапазоны или таблицу Excel и использовать коды, как показано в предыдущем разделе.

Но если вы не можете этого сделать, вы можете использовать свойство CurrentRegion или End объекта диапазона.

Приведенный ниже код скопирует текущую область на активном листе и вставит ее на Лист2.

Если вы хотите скопировать первый столбец вашего набора данных до последней заполненной ячейки и вставить его в 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.

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

В примерах в этой статье используются свойства из следующей таблицы.

Как выбрать ячейку на активном листе

Чтобы выбрать ячейку D5 на активном листе, можно использовать любой из следующих примеров:

Как выделить ячейку на другом листе той же книги

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

Или вы можете активировать рабочий лист, а затем использовать метод 1 выше, чтобы выбрать ячейку:

Как выбрать ячейку на листе в другой книге

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

Или вы можете активировать рабочий лист, а затем использовать метод 1 выше, чтобы выбрать ячейку:

Как выбрать диапазон ячеек на активном листе

Чтобы выбрать диапазон C2:D10 на активном листе, вы можете использовать любой из следующих примеров:

Как выделить диапазон ячеек на другом листе той же книги

Чтобы выбрать диапазон D3:E11 на другом листе той же книги, можно использовать один из следующих примеров:

Или вы можете активировать рабочий лист, а затем использовать метод 4 выше, чтобы выбрать диапазон:

Как выбрать диапазон ячеек на листе в другой книге

Чтобы выбрать диапазон E4:F12 на листе в другой книге, вы можете использовать любой из следующих примеров:

Или вы можете активировать рабочий лист, а затем использовать метод 4 выше, чтобы выбрать диапазон:

Как выбрать именованный диапазон на активном листе

Чтобы выбрать именованный диапазон "Тест" на активном листе, вы можете использовать один из следующих примеров:

Как выбрать именованный диапазон на другом листе в той же книге

Чтобы выбрать именованный диапазон "Тест" на другом листе той же книги, можно использовать следующий пример:

Или вы можете активировать рабочий лист, а затем использовать метод 7 выше, чтобы выбрать именованный диапазон:

Как выбрать именованный диапазон на листе в другой книге

Чтобы выбрать именованный диапазон "Тест" на листе в другой книге, можно использовать следующий пример:

Как выбрать ячейку относительно активной ячейки

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

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

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

Как выбрать ячейку относительно другой (не активной) ячейки

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

Как выбрать диапазон смещения ячеек из указанного диапазона

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

Если именованный диапазон находится на другом (не активном) рабочем листе, сначала активируйте этот рабочий лист, а затем выберите диапазон, используя следующий пример:

Как выбрать указанный диапазон и изменить размер выделения

Чтобы выбрать именованный диапазон "База данных", а затем расширить выборку на пять строк, можно использовать следующий пример:

Как выбрать указанный диапазон, сместить его, а затем изменить его размер

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

Как выбрать объединение двух или более указанных диапазонов

Чтобы выбрать объединение (то есть объединенную область) двух именованных диапазонов "Тест" и "Образец", вы можете использовать следующий пример:

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

возвращает сообщение об ошибке:

Ошибка метода объединения класса приложения

Как выбрать пересечение двух или более заданных диапазонов

Чтобы выбрать пересечение двух именованных диапазонов "Тест" и "Образец", можно использовать следующий пример:

Обратите внимание, что для работы этого примера оба диапазона должны находиться на одном листе.

Примеры 17–21 в этой статье относятся к следующему набору данных. В каждом примере указан диапазон ячеек выборки данных, которые будут выбраны.

Как выбрать последнюю ячейку столбца смежных данных

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

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

Как выделить пустую ячейку внизу столбца смежных данных

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

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

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

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

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

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

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

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

Как выделить прямоугольный диапазон ячеек

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

Этот код выберет ячейки с A1 по C4. Другие примеры выбора того же диапазона ячеек перечислены ниже:

В некоторых случаях может потребоваться выделить ячейки с A1 по C6. В этом примере метод CurrentRegion не будет работать из-за пустой строки в строке 5. В следующих примерах будут выбраны все ячейки:

Как выбрать несколько несмежных столбцов разной длины

Чтобы выбрать несколько несмежных столбцов разной длины, используйте следующий пример таблицы и макроса:

При использовании этого кода с образцом таблицы будут выбраны ячейки A1:A3 и C1:C6.

Примечания к примерам

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

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

При использовании метода Application.Goto, если вы хотите использовать два метода Cells в методе Range, когда указанный диапазон находится на другом (не активном) листе, вы должны каждый раз включать объект Sheets. Например:

Для любого элемента в кавычках (например, для именованного диапазона "Тест") также можно использовать переменную, значением которой является текстовая строка. Например, вместо

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

И если вы хотите использовать КЛЕТКИ, в этом случае код будет таким:

Выберите диапазон ячеек

Чтобы выбрать весь диапазон, необходимо определить адрес диапазона, а затем использовать свойство select. Например, если вы хотите выбрать диапазон от A1 до A10, код будет таким:

Выбрать непродолжительный диапазон

Чтобы выбрать непрерывный диапазон, вам нужно использовать запятую в адресах ячеек или диапазонов, а затем использовать свойство выбора. Допустим, если вы хотите выбрать диапазон от A1 до A10 и от C5 до C10, код будет таким:

И если вы хотите выбрать отдельные ячейки, которые не являются непрерывными, код будет таким:

Выберите столбец

Чтобы выбрать столбец, скажем, столбец A, вам нужно написать следующий код:

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

Выбрать строку

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

А для нескольких строк код будет таким:

Выбрать все ячейки рабочего листа

Допустим, вы хотите выделить все ячейки на листе, точно так же, как вы используете сочетание клавиш Control + A. Вам нужно использовать следующий код.

«Ячейки» относятся ко всем ячейкам на листе, и свойство select выбирает их.

Выбрать ячейки только с данными

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

Выберите именованный диапазон

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

В приведенном выше коде у вас есть именованный диапазон «my_range», а затем свойство select, и когда вы запускаете этот макрос, он выбирает указанный диапазон.

Выберите таблицу Excel

Если вы работаете с таблицами Excel, вы также можете выбрать их с помощью свойства select. Допустим, у вас есть таблица с названием «Данные», тогда код для выбора этой таблицы будет таким:

Если вы хотите выбрать столбец, а не всю таблицу, код будет примерно таким:

А если вы хотите выделить весь столбец, включая заголовок, то можете использовать следующий код:

Использование смещения для выбора диапазона

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

VBA упрощает выбор диапазона Excel. Вот два макроса, которые обеспечивают максимальную эффективность и гибкость.

< /p>

Использование VBA для выполнения каких-либо действий с диапазоном Excel — частая задача, но для этого вам нужен способ передать VBA, с какими ячейками вы хотите действовать. Использование свойства Selection приложения, вероятно, самый простой (но не единственный) способ.В этой статье я покажу вам два макроса: первый позволяет пользователю выбрать определенный диапазон перед выполнением макроса; второй позволяет пользователю выбрать диапазон до или после выполнения макроса. Вы можете делать практически все что угодно с диапазоном; Я решил применить цвет заливки, потому что это просто и не отвлекает от основной темы статьи — процесса выделения.

Подробнее о программном обеспечении

Я использую Office 365 (Excel 2016 для настольных ПК) в 64-разрядной системе Windows 10, но эти макросы будут работать в более ранних версиях. Вы можете работать со своей собственной книгой или загрузить демонстрационный файл .xlsm, .xls и .bas (модуль кода). Браузерная версия Excel не поддерживает макросы. Если вы никогда не создавали макрос, вы сможете выполнить шаги, описанные в этой статье, до конца.

SEE: Политика резервного копирования данных конечных пользователей (Tech Pro Research)

Действовать над существующим выбором

Чтобы использовать VBA для обработки существующего выбора, вам необходимо создать объект Range и установить для этого объекта существующий диапазон. Это проще, чем кажется. В этом случае операторы Dim и Set делают реальную работу:

Затемнить selectedRng как диапазон
Установить selectedRng = Application.Selection

Инструкция Dim объявляет объект Range с именем selectedRng. Оператор Set определяет диапазон, используя существующий выбор, предоставленный свойством Selection объекта Application. Вот и все! Как только вы определите объект Range, вы сможете действовать с ним. Листинг A меняет цвет заливки выбранного диапазона на желтый, но вы можете делать практически все что угодно. К сожалению, вы не можете удалить цвет заливки, нажав [Ctrl]+Z или нажав кнопку Отменить.

Список А

Sub ApplyColor1()
'Применить RGB(255,255,0) к выбранному диапазону.
Dim selectedRng As Range
'Обработка ошибок; на всякий случай.
При ошибке Возобновить дальше
'Определить выбранный диапазон.
Set selectedRng = Application.Selection
'Что-нибудь сделать с выделенным.
With selectedRng.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535 'То же, что и RGB(255,255,0)
.TintAndShade = 0 < br />.PatternTintAndShade = 0
End With
End Sub

Чтобы добавить код в книгу, нажмите F11, чтобы запустить VBE. В меню «Вставка» выберите «Модуль». Затем добавьте код. Вы можете импортировать макрос из загружаемого файла .bas или ввести его самостоятельно. Не копируйте с этой веб-страницы, потому что VBE не может интерпретировать специальные веб-символы и вернет ошибку. Если вы используете ленточную версию Excel, сохраните книгу как книгу с поддержкой макросов, прежде чем продолжить.

Перед выполнением этого макроса выберите одну ячейку или непрерывный или несмежный диапазон. Если ячейка или диапазон содержат данные, не беспокойтесь. Макрос ничего не перезапишет. Чтобы легко получить доступ к макросу, добавьте макрос на панель быстрого доступа или в пользовательскую группу на ленте. Если вы не знаете, как это сделать, прочтите статью Как добавить макросы Office на панель инструментов QAT для быстрого доступа. В этой статье также показано, как при необходимости отобразить вкладку «Разработчик». Вы найдете оба макроса в QAT и в пользовательской группе макросов на вкладке «Главная» в демонстрационном файле .xlsm. Вот как мы будем запускать макрос:

Выберите диапазон, в котором вы хотите применить желтый цвет заливки, например, B4:H4 и B16:H16 (рис. A). Чтобы создать несмежный диапазон, выберите любой диапазон. Затем, удерживая нажатой клавишу Ctrl, выберите второй элемент.
Перейдите на вкладку "Разработчик". (Прочитайте статью по ссылке выше, если вкладка «Разработчик» не отображается.)
В группе "Код" нажмите "Макросы".
В появившемся диалоговом окне выберите ApplyColor1 (рис. Б) и нажмите "Выполнить".

Рисунок А

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

Рисунок Б

Запустите макрос ApplyColor1.

Как видно на рисунке C, макрос применяет цвет заливки только к выбранному диапазону. То, что делает макрос, менее важно, чем два оператора, используемые для создания объекта Range из существующего диапазона, чтобы вы могли действовать на него. Это очень просто, но что произойдет, если вы выберете неправильный диапазон перед выполнением макроса? В следующем разделе рассматривается макрос, который это обрабатывает.

Рисунок C

Макрос выделяет выбранные диапазоны.

ПОСМОТРЕТЬ: 20 быстрых советов по Windows для опытных пользователей (бесплатный PDF-файл TechRepublic)

Действовать при выборе диапазона ввода

Пользователям не всегда нужно выбирать диапазон перед выполнением макроса; выбор больших или нескольких диапазонов может быть неудобным. Макрос из листинга B немного более удобен для пользователя.Это позволяет пользователям вводить диапазон или имя диапазона или выбирать диапазон после выполнения макроса. Он также подходит для пользователей, которые выбирают диапазон перед выполнением макроса.

Список Б

Sub ApplyColor2()
'Применить RGB(255,255,0) к выбранному или введенному диапазону.
Затемнить selectedRng как диапазон
Установить selectedRng = Application.Selection
'Обработка ошибок при захвате клавиши Cancel.
При ошибке GoTo errHandler
'Определить диапазон.
Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)
'Действие с выбранным или введенным диапазоном.
With selectedRng.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535 'То же, что и RGB(255,255,0)
.TintAndShade = 0 < br />.PatternTintAndShade = 0
End With
'Остановить перед запуском обработки ошибок.
Exit Sub
errHandler:
'Выход из подпроцедуры, когда пользователь нажимает кнопку отмены InputBox.
If Err.Number = 424 Then
Выйти из Sub
Else: MsgBox "Error: " & Err.Number, vbOK
End If
End Sub

Введите ApplyColor2 в VBE и запустите его так же, как и ApplyColor1. Этот второй макрос похож на первый, но лучше, потому что он более гибкий. Вы можете выбрать диапазон до или после выполнения макроса. Макрос размещает активный диапазон, устанавливая его в качестве диапазона ввода по умолчанию для поля ввода, как вы можете видеть на рисунке D. На этом этапе вы можете сохранить настройку диапазона по умолчанию и нажать «ОК». Или вы можете ввести новый диапазон, набрав диапазон или имя диапазона с клавиатуры или выбрав диапазон с помощью мыши. Если вы нажмете «Отмена» вместо «ОК», сработает обработчик ошибок, и макрос завершит работу до применения цвета заливки.

Рисунок D

По умолчанию поле ввода соответствует выбранному диапазону.

Чтобы узнать больше о ссылках на диапазоны, листы и даже книги, прочитайте следующие статьи:

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