Команда resize в excel vba
Обновлено: 04.07.2024
An Excel Range refers to one or more cells (ex. A3:B4)
Cell Address
A1 Notation
In VBA you can refer to any cell using the Range Object.
' Refer to cell B4 on the sheet named 'Data' in another OPEN workbook MsgBox Workbooks ( "My Data" ) . Worksheets ( "Data" ) . Range ( "B4" )R1C1 Notation
Range of Cells
A1 Notation
R1C1 Notation
Writing to Cells
To write values to a cell or contiguous group of cells, simple refer to the range, put an = sign and then write the value to be stored:
Reading from Cells
To read values from cells, simple refer to the variable to store the values, put an = sign and then refer to the range to be read:
Note: To store values from a range of cells, you need to use an Array instead of a simple variable.
Non Contiguous Cells
To refer to non contiguous cells use a comma between the cell addresses:
Intersection of Cells
To refer to non contiguous cells use a space between the cell addresses:
Offset from a Cell or Range
Using the Offset function, you can move the reference from a given Range (cell or group of cells) by the specified number_of_rows, and number_of_columns.
Offset Syntax
Offset from a cell
Offset from a Range
' Move Reference to Range A1:D4 by 4 rows and 4 columnsSetting Reference to a Range
To assign a range to a range variable: declare a variable of type Range then use the Set command to set it to a range. Please note that you must use the SET command as RANGE is an object:
Resize a Range
Resize method of Range object changes the dimension of the reference range:
Top-left cell of the Resized range is same as the top-left cell of the original range
Resize Syntax
Great Product. AutoMacro doesn't just write your code, it teaches as you go!" - Tony, UK
OFFSET vs Resize
Offset does not change the dimensions of the range but moves it by the specified number of rows and columns. Resize does not change the position of the original range but changes the dimensions to the specified number of rows and columns.
All Cells in Sheet
The Cells object refers to all the cells in the sheet (1048576 rows and 16384 columns).
UsedRange
UsedRange property gives you the rectangular range from the top-left cell used cell to the right-bottom used cell of the active sheet.
CurrentRegion
CurrentRegion property gives you the contiguous rectangular range from the top-left cell to the right-bottom used cell containing the referenced cell/range.
' If there is a filled path from D4:F16 to B2 AND L14Range Properties
You can get Address, row/column number of a cell, and number of rows/columns in a range as given below:
Last Cell in Sheet
You can use Rows.Count and Columns.Count properties with Cells object to get the last cell on the sheet:
Debug . Print "Columns in the sheet: " & Columns . Count Debug . Print "Address of Last Cell in the sheet: " & Cells ( Rows . Count , Columns . Count )Last Used Row Number in a Column
END property takes you the last cell in the range, and End(xlUp) takes you up to the first used cell from that cell.
Last Used Column Number in a Row
lastCol = Cells ( 1 , Columns . Count ) . End ( xlToLeft ) . ColumnEND property takes you the last cell in the range, and End(xlToLeft) takes you left to the first used cell from that cell.
You can also use xlDown and xlToRight properties to navigate to the first bottom or right used cells of the current cell.
Cell Properties
Common Properties
Here is code to display commonly used Cell Properties
Cell Font
Cell.Font object contains properties of the Cell Font:
Copy and Paste
Paste All
Ranges/Cells can be copied and pasted from one location to another. The following code copies all the properties of source range to destination range (equivalent to CTRL-C and CTRL-V)
Range ( "A1:D20" ) . Copy destination : = Worksheets ( "Sheet2" ) . Range ( "B10" )Paste Special
Selected properties of the source range can be copied to the destination by using PASTESPECIAL option:
Worksheets ( "Sheet2" ) . Range ( "B10" ) . PasteSpecial Paste : = xlPasteValuesHere are the possible options for the Paste option:
AutoFit Contents
Size of rows and columns can be changed to fit the contents using AutoFit:
More Range Examples
It is recommended that you use Macro Recorder while performing the required action through the GUI. It will help you understand the various options available and how to use them.
For Each
It is easy to loop through a range using For Each construct as show below:
At each iteration of the loop one cell in the range is assigned to the variable cell and statements in the For loop are executed for that cell. Loop exits when all the cells are processed.
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
| Свойство | Описание |
|---|---|
| Address | Возвращает адрес ячейки (диапазона). |
| Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
| Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
| Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
| Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
| ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
| Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
| CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
| EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
| EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
| Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
| Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
| Name | Возвращает или задает имя ячейки (диапазона). |
| NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
| Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
| Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
| Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
| RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
| Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
| Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
| Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
This tutorial will demonstrate how to use the Resize Property of the Range Object to change to return a new range resized from the original Range object.
Syntax
Syntax for the Resize property is:
RowSize and ColumnSize must be greater than zero. Each input is optional (ex. you can omit RowSize to only change the number of columns, or vice-versa).
Resize Number Of Rows And Columns
Following example expands the range with one cell A1 to range A1:D10 by increasing row count to 10 and columns count to 5.
Resize Number Of Rows Only
Following example changes the number of rows only:
Resize Number Of Columns Only
Following example changes the number of columns only:
Resize Table Range To Exclude Header
If you have a table on active sheet with a header row, the code will first select the entire table then move down one row to exclude the header using Range.Offset method. It will then use the Range.Resize property to reduce the size by one row.
' Click on any cell of the table before running the macro ' Move down one row by using Offset and then reduce range size by one row Set tbl = ActiveCell . CurrentRegion . Offset ( 1 , 0 ) . Resize ( tbl . Rows . Count - 1 , _Write 2-D Array To Range
Another common usage is to write a 2 dimensional array to a sheet. Since the range to be written should match the size of the array, which is normally not known in advance, the Resize method is used to set the output range.
Worksheets ( "Output" ) . Range ( "A1" ) . Resize ( UBound ( data , 1 ) , UBound ( data , 2 ) ) . Value = dataWritten by: Vinamra Chandra
VBA Code Generator
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
Для существующего именованного диапазона вам может потребоваться изменить размер или расширить его, чтобы включить больше столбцов и строк. В этой статье мы покажем вам три метода изменения размера существующего именованного диапазона в Excel.
Измените размер существующего именованного диапазона с помощью функции диспетчера имен
Чтобы изменить размер существующего именованного диапазона с помощью функции диспетчера имен в Excel, сделайте следующее.
1. Нажмите Формулы > Менеджер имен. Смотрите скриншот:
2. в Менеджер имен диалоговом окне выберите имя диапазона, размер которого вы хотите изменить, а затем щелкните значок кнопку.
3. в Имя менеджера - относится к выберите новый диапазон для этого имени диапазона, а затем закройте это поле.
4. Когда он вернется в Менеджер имен диалогового окна, нажмите кнопку, чтобы сохранить новый диапазон, и, наконец, нажмите кнопку Закрыть кнопку, чтобы закрыть диалоговое окно. Смотрите скриншот:
Вы можете видеть, что указанный именованный диапазон немедленно изменяется.
Изменить размер существующего именованного диапазона с помощью кода VBA
Помимо вышеуказанного метода, код VBA также может помочь вам изменить размер указанного именованного диапазона.
1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Приложение Microsoft Visual Basic окно.
2. в Приложение Microsoft Visual Basic окна, нажмите Вставить > Модуль.
3. Вставьте ниже код VBA в окно модуля.
VBA: изменение размера именованного диапазона
Ноты:
1. «10, 6» в этой строке .RefersTo = .RefersToRange.Resize (10, 6) означает, что указанный именованный диапазон будет изменен, чтобы иметь 10 строк и 6 столбцов.4. После изменения переменных нажмите кнопку F5 ключ для запуска кода. Во всплывающем диалоговом окне введите имя диапазона, размер которого вы хотите изменить, в Имя поле, а затем щелкните OK кнопку.
Затем размер именованного диапазона немедленно изменяется в соответствии с указанными вами условиями.
Измените размер существующего именованного диапазона с помощью Kutools for Excel
Все названия диапазонов перечислены в Область переходов of Kutools for Excel. И вы можете легко изменить их размер в области навигации.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. После установки Kutools for Excel, Область переходов по умолчанию отображается в левой части Excel. Пожалуйста, нажмите на Имя менеджера, выберите имя диапазона, размер которого нужно изменить, а затем нажмите кнопку кнопку.
2. Во всплывающем Kutools for Excel диалоговом окне выберите новый диапазон для этого имени диапазона, а затем щелкните значок OK кнопку.
3. Затем нажмите в Область переходов чтобы завершить настройку.
Внимание: Если вы не видите Область переходов, Пожалуйста, нажмите Kutools > Навигация чтобы включить его.
Освободи Себя Область переходов of Kutools for Excel это мощная утилита, с ее помощью вы можете не только легко изменять размер именованного диапазона, но также создавать автоматический текст для будущего использования, легко переключаться между книгами и листами и так далее.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: