Vba excel что такое usedrange
Обновлено: 08.07.2024
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить
Одинаковые переменные для всех примеров
Dim lLastRow As Long 'а для lLastCol можно применить тип Integer, 'т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
Способ 1:Определение последней заполненной строки через свойство End
определяя таким способом нам надо знать что:
1 - это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.
Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) - то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
1 - это номер строки, последнюю заполненную ячейку в которой мы определяем.
Способ 2:Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
Определение последней заполненной строки через SpecialCells
Определение последнего столбца через SpecialCells
Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку - Row либо столбец - Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1.
Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.
Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
небольшой практический код , который поможет вам понять, как использовать полученную переменную:
Sub Get_Last_Cell() Dim lLastRow As Long Dim lLastCol As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox "Заполненные ячейки в столбце А: " & Range("A1:A" & lLastRow).Address lLastCol = Cells.SpecialCells(xlLastCell).Column MsgBox "Заполненные ячейки в первой строке: " & Range(Cells(1, 1), Cells(1, lLastCol)).Address MsgBox "Адрес последней ячейки диапазона на листе: " & Cells.SpecialCells(xlLastCell).Address End Sub
А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:
Sub Copy_To_Last_Cell() Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub
А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:
Sub Copy_To_Last_Cell() Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub
Следующим кодом используем инструмент автозаполнение(протягивание) столбца В на основании значения в ячейке B2 и определяя последнюю ячейку для заполнения на основании столбца А
Sub AutoFill_B() Dim lLastRow As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("B2").AutoFill Destination:=Range("B2:B" & lLastRow) End Sub
Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение "", Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Let’s say you want to autofit the column A, the code would be something like below:
In the above line of code, you have used the EntireColumn property to refer to the entire column of the cell A1.
As you are within a worksheet so you can also use the columns property and write a code like below.
AutoFit a Row
In the same way, you can write a code to autofit a row. Let’s say you want to autofit the row 5, the code would be:
And if you want to use the row property, then you can use the code like the following.
AutoFit UsedRange (Rows and Columns)
Now let’s say, you only want to autofit those columns and rows where you have data. In VBA, there is a property called used range that you can use. So the code would be.
And if you want to use a specific worksheet then the code would be.
AutoFit Entire Worksheet
And if you want to refer to all the columns and rows of the worksheet then you can use the “CELLS” property. Here’s the code.
Or you can also use VBA’s WITH statement to write a code like below.
More on VBA Range and Cells
If you are trying to prepare for an interview check out these VBA Interview Questions and Answers. And if you found this tutorial helpful, you can support us to create more tutorials like this.
To apply borders to a cell using VBA in Excel, you can use two different ways. The first is the “Borders“ property and the second is the “BorderAround” method. The only difference between these two ways is in the “Borders” property you can even apply the border to one side of the cell and in the border around it applies to the cell completely.
In this tutorial, we will explore both ways and look at what are the different ways to add the border.
VBA Border Property
Now when you run this code, it will add a border to the bottom of the cell A1.
Using Different Colors with Color Index/Color
Just like you normally choose the color for the border when you do it manually. In the border property, you can further use the color index and color property to use a color other than the default color.
Consider the below code.
When you run this macro, it adds a border to the bottom of the cell with a red color.
Add Border Inside a Cell
If you want to apply border inside a cell, in that case, you need to sue “xlDiagonalUp” and “xlDiagonalDown” as the xlBorderIndex.
In VBA’s BorderAround property you have 5 arguments to which are optional but two need to be specified to apply the border to a cell in the right way.
The following code loops through all the cells in the range and applies the border to each cell one by one.
Apply Border to All the Cells with Text
The following code uses a FOR LOOP, USED RANGE, IFEMPTY, and IF STATEMENT to apply borders to the only cells where you have value.
Remove Borders
You just need to use line style “xlNone”.
And if you want to remove the border from all the cells in a worksheet where you have a value, consider the following code.
More on VBA Range and Cells
If you are trying to prepare for an interview check out these VBA Interview Questions and Answers. And if you found this tutorial helpful, you can support us to create more tutorials like this.
В данном разделе мы рассмотрим что такое VBA, посколько именно на нем и пишутся макросы.
Что такое VBA?
VBA - язык (расшифровывается как Visual Basic for Application) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен для автоматизации процессов в пакете MS Office. VBA широко используется в Excel, а также в Access, Word и других программах пакета.
Чтобы работать с VBA кодом, нам нужен редактор, который уже установлен по умолчанию. Вы можете открыть его, нажав комбинацию клавиш " ALT + F11 ".
Объекты (Objects)
Давайте разберем, что же такое объект. Объект - это элемент, структурная частица Excel, а именно: книга, лист, диапазон, ячейка. Данные объекты имеют иерархию, т.е. подчиняются друг другу. Схематично структуру иерархии Excel можно изобразить следующим образом:
Главный объект это Application, что соответствует самой программе Excel. Далее следует Workbooks (книга), Worksheets (лист), Range (диапазон, или отдельная ячейка). Например, чтобы обратиться к ячейке "A1" на листе нам нужно будет прописать следующий путь с учетом иерархии:
Application.Workbooks("Архив").Worksheets("Аркуш1").Range("A1").
Таким образом, мы научились обращаться до наименьшего объекта в Excel - ячейки.
Коллекции (Collections)
- ChartObjects (элемент коллекции объекта ChartObject)
- Range
- PageSetup
- PivotTables (элемент коллекции объекта PivotTable).
Свойства (Properties)
Каждый объект имеет свойства . Например, объект Range имеет свойство Value или Formula.
Worksheets(“Sheet1”).Range(“A1”).Value або Worksheets(“Sheet1”).Range(“A1”).Formula
В данном примере, свойство отображает значение, которое введено в ячейку или введенную формулу.
Также, через свойство Formula можно не только получить формулу, но и записать ее:
Range(“B12”).Formula = “=2+6*100” - вписываем формулу =2+6*100 в ячейку B12.
Методи (Methods)
Теперь давайте рассмотрим, каким образом мы можем управлять содержимым диапазона или ячейки. Для этого в VBA существуют, так-называемые методы (команды "что сделать"). При написании кода методы отделяются от объекта точкой, например:
Range("A1").Select или Cells(1, 1).Select
Данный метод указывает выбрать (Select) ячейку "A1".
Далее, давайте, удалим значение в данной ячейке. Для этого напишем следующий код:
Selection.ClearContents
Здесь программа "берет" то, что мы выделили (Selection) и удаляет его содержимое (ClearContents ).
Читайте также: