VBA Excel. Переменная диапазона ячеек (As Range)

Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

Присвоение диапазона ячеек переменной

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как 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. Обратиться к ней можно так:

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

обращение к первой строке присвоенного диапазона размерностью 5х5:

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

Работа с диапазоном в переменной

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

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

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

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

Пример 1 – работа со значениями

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

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

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

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

Пример 3 – копирование и вставка диапазона из переменной

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

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

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

Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.

Размер диапазона в переменной

При получении диапазона с помощью метода Application.InputBox и присвоении его переменной диапазона, бывает полезно узнать его размерность. Это можно сделать следующим образом:

Запустите процедуру, выберите на рабочем листе Excel любой диапазон и нажмите кнопку «OK». Информационное сообщение выведет количество строк и столбцов в диапазоне, присвоенном переменной myRange.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

57 комментариев для “VBA Excel. Переменная диапазона ячеек (As Range)”

  1. Добрый день! Подскажите пожалуйста, как можно задать диапазон (например всего одну строку) для поиска Cells.find.

  2. Евгений (автор статьи)

    Привет, Иван!
    Для второй строки: Rows(2).Find(stroka)
    Для диапазона: Range("A2:G10").Find(stroka)
    stroka – это переменная или искомый текст в прямых кавычках.

  3. Добрый день, можете, пожалуйста, подсказать?
    У меня есть книга Excel, мне нужно создать макрос, который мог бы при определённых значениях ячеек делать заливку, т.е. у меня есть таблица, в ней стоят месяца и т.п. и есть значения (Т,А,С): ячейки, где стоят Т, должны быть зелёного цвета; Где А-красного; Где С -жёлтого, и как это можно растянуть на всю книгу

    1. Евгений (автор статьи)

      Добрый день, Антон!
      В этом случае можно обойтись без макроса. Попробуйте использовать условное форматирование. Только правила задавайте не для всей или части строки, а для конкретного столбца, в котором будут находиться ячейки с искомыми значениями.

  4. Здравствуйте. У меня есть таблица, это расположение сидений в кинотеатре. Ну что то наподобие сетки, когда приходишь в кино и покупаешь билет.
    Рядом есть в двух отдельных ячейках мне нужно указать ряд и номер сидения, на котором я хочу сидеть. Я очень хочу понять какой необходим макрос, чтобы я мог набирать в этих ячейках адрес конкретного сидения и это сиденье подсвечивалось значением “Х” на первой таблице

    1. Евгений

      Привет, Дима!
      Если ячейкам с местами присвоить имена, например: ряд1кресло1, ряд1кресло2 и т.д., добавить на лист кнопку с заголовком «Забронировать», тогда для кнопки можно записать следующий код:

      В примере кода: ячейка с номером ряда – «M2», с номером кресла – «N2». При нажатии кнопки, если такое место существует, в него будет записана буква «X».

  5. Валерия

    Здравствуйте. Не подскажите, как присвоить переменной имя умной таблицы или диапазона? просто лист с имеющейся таблицей будет копироваться (по количеству дат) и заполнение затрудняется из-за этого.

    1. Евгений

      Здравствуйте, Валерия!
      Если нужно объектной переменной присвоить диапазон ячеек умной таблицы, то так:

  6. Добрый день, подскажите команду для этого
    Заполнить 200 ячеек в столбце А с текстом “ячейка №1”, “ячейка №2”,… , “ячейка №200

    1. Евгений

      Привет, Максим!

    2. Range(Cells(3, int_Var), Cells(26, int_Var))
      почему практически такие примеры с cells приводят
      а Range(str_Var+cstr(int_Var)) не встречал

      1. Евгений

        Привет, OldBell!
        Так тоже можно:

        Только вместо «+» лучше использовать «&».

        1. можно, работает, просто везде через cells примеры, подумал, возможно некорректно или еще чего.
          сам пользуюсь давно. но еще один вопрос отослал только, что, шаг цикла 2. Вот грешу на Range может, чего куда записываети и спросил

      2. Александр

        Добрый вечер! Подскажите команду для вставки данных объектом кнопка в активную ячейку (выбирать курсором а не задавая диапазон в range)

        1. Привет, Александр!
          К ячейке, выбранной курсором, можно обратиться как к объекту Selection:

        2. Александр

          А как сделать чтобы объект Selection вставлял данные в ячейку не как текст а как число?

        3. Александр

          Ввожу число 1,5 или 1,75 выдает ошибку
          Compile error:
          Expected: end of statement
          С целыми все работает.

          1. Евгений

            Александр, в VBA Excel разделитель дробной части – точка, а не запятая. В ячейке точка сама превратится в запятую.

        4. Здравствуйте!
          А каким образом можно вставить диапазон ячеек A1:C3 в переменную (не только значения, но и форматирование с примечаниями), а потом вставить в D4:F6?
          Спасибо!

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

              Как его правильно сделать через переменную?

              1. Евгений

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

                Что делает каждая из трех строк:

                1. Я пытаюсь поменять местами диапазоны.
                  Например, в диапазоне1 (A1:C3) у меня ячейки заполнены единичками и окрашены красным, а в диапазоне2 (D4:F6) зелёные двойки.
                  Теперь я хочу, чтобы зелёные двойки были в диапазоне1, а красные единички – в диапазоне2.
                  Для этого я пытаюсь:
                  1. сохранить содержимое диапазона1 в переменную (буфер)
                  2. скопировать диапазон2 в диапазон1 (везде получаются двойки)
                  3. вставить содержимое переменной (куда я сохранил единички) в диапазон2

                2. Евгений

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

                  1. Мне не очень нравится такой вариант – вдруг там есть (были) данные?
                    Правильно ли я понял, что нельзя записать данные в переменную, а потом извлечь
                    потому что в ней сохраняются не сами ячейки, а как бы ссылки на них?
                    И если да, то как ещё можно реализовать такой обмен?

                    1. Евгений

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

                      Есть два варианта решения без использования дополнительного диапазона на том же листе, где расположены исходные диапазоны:

                      1. Создать новый лист, использовать третий диапазон на нем, а после использования лист удалить.

                      2. Или копировать значения и форматы в обычные переменные типа Variant, но есть условие: форматы (цвет заливки, цвет текста, шрифт, границы) у всех ячеек одного диапазона должны быть одинаковыми (значения ячеек могут быть разными, так как при их присвоении переменная Variant будет преобразована в массив):

                      Пример по вашему условию: обмен цветом заливки и значениями между двумя диапазонами.

                  2. Добрый день!
                    Прошу Вашей помощи.
                    есть лист, данные на котором меняются при изменении значения выпадающего списка (меняем фамилию, автоматически изменяются колонки с табельным номером, должностью, стажем и т.д.)
                    Выпадающий список сделан на основе диапазона с именем “список”, находится в ячейке F2.
                    Есть необходимость сохранять полученные значения в PDF.
                    Текущий лист сохраняю так:

                    Очень нужно сохранить полученные значения для всего диапазона (т.е. для всех работников) одним макросом (можно в один файл PDF).
                    Спасибо!!!

                    1. Евгений

                      Добрый день, Petr!
                      Файлы PDF с именами работников и данными по ним можно создать так:

                    2. Здравствуйте!
                      У меня некорректно считываются данные в переменную из несмежного диапазона.
                      Для примера, я написал макрос, который
                      заполняет часть область цифрами, потом забирает часть из них в переменную,
                      а после показывает, что удалось забрать, но он работает некорректно.

                      Как его исправить?
                      Спасибо!

                      1. Евгений

                        Привет!
                        Данные считываются корректно. Используйте для их отображения цикл For Each… Next:

                        1. Спасибо за ответ!
                          А можете объяснить отличие от моего варианта
                          и вообще логику работы for each … next в данном случае?
                          И ещё – как убрать пустые элементы?
                          Допустим, я между 4-й и 5-й строчками в своём макросе добавляю строку с очисткой ячейки A3

                          в этом случае 4-й элемент остаётся пустым,
                          а когда я пытаюсь проверять Rng1 (пустой он или нет),

                          то после 3-го элемента идёт сразу 5-й, а
                          мне бы хотелось, чтобы шло 6 элементов подряд.

                          1. Евгений

                            Цикл For Each… Next не работает в объединенном диапазоне из несмежных диапазонов, так как у него непонятная индексация ячеек. Попробуйте вставить этот код:

                            Возможно, это один из глюков VBA Excel.

                            Шесть элементов подряд:

                        2. Алексей

                          Подскажите пожалуйста, такая ситуация:
                          Есть динамическая таблица, размеры которой меняются, формулой определен адрес ячейки до которой нужно протянуть формулу.
                          Формула находится в ячейке A23
                          Адрес ячейки до которой протягивать формулу в ячейке K4
                          Как “объяснить” макросу, что нужно взять адрес ячейки (до которой протягивать) из ячейки на листе? Чувствую, что это просто, но знаний не хватает пока.
                          А как будет выглядеть код макроса, если диапазон полностью нужно взять из ячеек на листе?

                          1. Евгений

                            Добрый день, Алексей!
                            Если в ячейке «K3» – адрес первой ячейки диапазона, а в ячейке «K4» – последней, тогда так:

                            1. Алексей

                              Огромное вам спасибо, Евгений! Вы даже не представляете на сколько сильно вы мне помогли! 🙂
                              С Праздником Великой Победы Вас и ещё раз благодарю за оперативный ответ!

                            2. Добрый день!
                              Подскажите пожалуйста как сделать:
                              В листе2 есть некоторые данные (число строк меняется) со столбца А по столбец АР
                              Первая строка всегда = 16, последняя определяется по “n”:

                              Необходимо скопировать диапазон А16:АР&n и вставить в Лист1 с ячейки А&x?
                              x – тоже определяется по последней пустой ячейке.

                              1. Евгений

                                Добрый день, Михаил!
                                Два уточняющих вопроса:
                                На листе2 в первом столбце до A16 все ячейки заполнены?
                                На листе1 в первом столбце до первой вставки скопированных ячеек есть заполненные ячейки или вставка может начаться с A1?

                                1. Добрый день, Евгений!
                                  На листе2 до 16 строки заполнена “шапка” заголовки и сведения о данных.
                                  На листе1 вставка не с первой строки, а с первой пустой, определяю также, как на листе2 по переменной.

                                  1. Евгений

                                    Михаил, есть способы проще найти номер последней строки.

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

                                    1. Спасибо, Евгений.
                                      Но не получилось, снова ошибка..
                                      Я проблему решил, но очень не красивым способом:
                                      Записал в свободную ячейку, например C1 на листе1 адрес A16:AP затем задал переменную adr=Worksheets(“Лист1”).Range(“C1”).value и еще одну переменную adres=adr & n
                                      затем сделал так:

                                      и только так все стало работать!
                                      Любые попытки в range прописать cells или еще раз range заканчиваются дебагом…
                                      Офис 2010.

                                      1. Евгений

                                        Михаил, попробуйте еще два варианта:

                                    2. Алексей

                                      Добрый день, Евгений!
                                      Прошу посоветовать, есть 2 книги excel одна содержит данные, столбец с датами и столбец с именами. Другая содержит сводную информацию в котором даты в столбец, а имена в строку, мне нужно чтобы сумма имя=дата (3 января 4 Иванов, 5мая 6 Иванов и т.д.) с первой книги, записывался в ячейку второй книги имя=дата.
                                      Как можно это реализовать, заранее спасибо!

                                    3. диапазону (D3:D70) присвоено имя “PS”

                                      как обратиться к 4-ой ячейке диапазона

                                      что-то типа этого
                                      Cells(4, “PS”)
                                      / но так не работает

                                    4. PS.Cells(4)
                                      машина ругается на “голое” имя
                                      пришлось “привязать”

                                      так работает
                                      спасибо

Добавить комментарий

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