Vba excel что такое usedrange
How to use UsedRange Property in VBA in Excel
Home ➜ VBA Tutorial ➜ How to use UsedRange Property in VBA in Excel
In VBA, the UsedRange property represents the range in a worksheet that has data in it. The usedrange starts from the first cell in the worksheet where you have value to the last cell where you have value. Just like the following example where you have used range from A1 to C11.
Note: UsedRange property is a read-only property.
Write a Code with UsedRange
Use the following code.
The above code clears everything from the used range from the active sheet.
Copy UsedRange
Use the following code to copy the entier UsedRange.
Count Rows and Columns in the UsedRange
There’s a count property that you can use to count rows and columns form the used range.
The above two lines of code shows a message box with the count of rows and columns that you have in the used range.
Activate the Last Cell from the UsedRange
You can also activate the last cell from the used range (that would be the last used cell in the worksheet). Consider the following code.
This code takes the rows and columns count using the UsedRange property and then use those counts to select the last cell from the used range.
Refer to UsedRange in a Different WorkSheet
If you are trying to refer to the used range in a worksheet other than the active sheet then VBA will how an error like the following.
So, the worksheet where you are referring to must be activate (only then you can use the UsedRange property).
That mean you can’t refer to the used range in a workbook that is closed. But you can use open a workbook first and then, activate the worksheet to use the UsedRange property.
Get Address of the UsedRange
Use the following line of code to get the address of the used range.
Count Empty Cells from the Used Range
The following code uses the FOR LOOPS (For Each) and loops through all the cells in the used range and counts cells that are empty.
When you run this code, it shows a message box with the total count of cells and how many cells are empty out of that.
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?
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
Одинаковые переменные для всех примеров
Dim lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Определение последнего столбца через SpecialCells
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Способ 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 не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Статья помогла? Поделись ссылкой с друзьями!
VBA Used Range – Count Number of Used Rows or Columns
UsedRange – Find Last Used Cell, Column or Row
The following code will return a message box indicating the total number of rows used in a worksheet. Empty rows are considered used if data follows the empty row.
Do you have to run a loop down a sheet but don’t know where the data stops? ActiveSheet.UsedRange.Rows.Count might help.
Put this in a module:
Find First Empty Cell
Using VBA you may need to write to the first empty cell, or after the last row used in a column. There is no need to loop to find this, the following code does it for you.
In this example the code will write “FirstEmpty” in the first empty cell in column “d”
Count Used Columns In Worksheet
The following code will return in a message box the total number of columns used in a worksheet. Empty columns are considered used if data follows the empty column.
Last Used Cell – Problems
When I need to For..Next..Loop through an entire column I usually use ActiveSheet.UsedRange.Rows.Count to find where to stop. I’ve always had good luck with this approach.
I am also aware that occasionally Excel thinks the last row exists somewhere, but the row is actually empty. I’ve seen this a few times after importing data. From BeyondTechnology:
The Worksheet object’s UsedRange does not always work because the used range (or “dirty area”) of a spreadsheet may be larger than the area actually populated with your records.
VBA Code Generator
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Controlling Your Spreadsheet With VBA UsedRange
The VBA Tutorials Blog
UsedRange Object
UsedRange is a special Range property that specifies the range of cells on a worksheet that have been, well, used. Truth is, certain modifications to a cell also designate it as “used” according to VBA. We’ll talk about this more in a few minutes, but a better description of the VBA UsedRange property is a range of cells, on the specified sheet, that have content, formatting, comments, or certain other modifications. It’s a great way to gain better control of your spreadsheet’s data.
where targetSheet is the name of the sheet you want to work with.
To demonstrate what VBA UsedRange actually does, add a couple values to an empty spreadsheet and run this short macro:
This macro selects the used range on your spreadsheet. You can see that VBA attempts to calculate the first cell and the last cell with data on your spreadsheet and selects everything in that range. Notice in the image below that any empty cells in the used range are also included when using the UsedRange property. Even though you might think it would, the VBA UsedRange property doesn’t just capture the union of all cells containing data.
The Shape of the Range
UsedRange with Unfilled Cells
Make powerful macros with our free VBA Developer Kit
Working with Rows and Columns
UsedRange is handy for finding the first and last rows and columns used on a worksheet. By extension, it can be used to count rows and columns to help iterate through for-loops.
First Row and First Column
The number for the last row and column requires a bit more work. One way to get the last row and column of a UsedRange is by using the count property.
Counting Rows and Columns
This returns 25 and 4 in our example above.
Last Row and Last Column
As can be seen in our example image, the last row is indeed Row 25. The last column, however, is not Column 4 (D). It’s Column 5 (E). To be more robust, you’d need to to add the first row/column numbers to the row/column counts.
Now we get our expected <25, 5>instead of <25, 4>result. Remember to subtract 1 from the final result, otherwise you’ll double-count the first row and column.
Now that we’ve demonstrated the logic for calculating the last used row and column in a UsedRange, let’s show you a much easier way. To record the last used row and column in a Used Range, simply use the following shortcut:
Counts for For-Loops
You would need two nested loops to loop through both the rows and the columns in your used range, like this:
If you only want to process data to cells in the UsedRange that are not empty, apply the VBA IsEmpty function.
Properties and Methods
Once you know how to navigate a UsedRange, applying properties using VBA is a breeze. Properties let you do things like change the entire range to bold font. Just note that the properties will be applied to the entire range, even if cells are blank. It would be tedious to go back and fix formatting for an entire sheet because of a single poorly thought out line of code.
This macro snippet changes all cells on the worksheet containing data (or formatting plus empty cells between used cells) to bold. You can do the same thing with colors, cell fill colors, number formats, etc. Again, be aware the property will be applied to the entire used range, so if you have a nicely formatted table with 5000 cells, make sure you want all 5000 cells to be bold before running those lines of code!
Clearing the Range
Another very common use of UsedRange is to clear the contents of a sheet. When using UsedRange, you don’t need to find the last row/column like we did above. After setting rng as a named object, simply execute:
Make powerful macros with our free VBA Developer Kit
Navigating the Range
Let’s imagine a user shifts the table to a different position on your worksheet for some reason. If you hardcoded your macro to grab population relative to the sheet, you may now be inadvertently grabbing the wrong data. It would be more robust to use the table’s own structure as the point of reference:
Hidden Changes
Format Changes Impacted VBA UsedRange
UsedRange makes it easy to find the first row/column and to apply formatting to any useful cells on a sheet. It also makes finding the last row/column pretty easy, and, most importantly, it helps you set up your for loops.
If you found this tutorial helpful, subscribe using the form below and we’ll share similar VBA tips to help you get the most out of the programming language.
Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we’ll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.
This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog. Visit him on LinkedIn and his personal page.
VBA UsedRange
UsedRange, as the name suggests, are the ranges which as some kind of values in them, the empty cells are not included in the used ranges, so in VBA Used ranges is the property of the range object in VBA for those range of cells in rows and columns which are not empty and have some values in it.
UsedRange in VBA Excel
The UsedRange in VBA is a property of the worksheet that returns a range object representing the range used (all Excel cells used or filled in a worksheet) on a particular worksheet. It is a property representing the area covered or bounded by top-left used cell and last right used cells in a worksheet.
We can describe ‘Used cell’ as a cell containing any formula, formatting, value, etc. We can also select the last used cell by pressing CTRL+END keys on the keyboard.
Following is an illustration of a UsedRange in a worksheet:
We can see in the above screenshot that the UsedRange is A1:D5.
Examples of Excel VBA UsedRange Property
Example #1
Let us say we have an Excel file containing two worksheets, and we wish to find and select the used range on Sheet1.
Let us see what the Sheet1 contains:
On doing this, a window opens as follows:
The immediate window looks like:
Code:
The first statement of the code will activate Sheet1 of the file, and the second statement will select the used range in that active sheet.
On writing this code, we see that the range used in Sheet1 gets selected as follows:
Example #2
Now, let’s say in this example, we wish to find the total number of rows used in Sheet1.To do this, we follow the below steps:
Code:
Code:
Code:
Code:
So, we can see in the above screenshot that ‘5’ is returned in the message box, and as we can see in Sheet1, the total number of rows in the used range is 5.
Example #3
Similarly, if we wish to find the total number of columns used in Sheet1, we will follow the same steps as above except for a slight change in the code as follows:
Code:
Now when we run this code manually or by pressing F5, we get the total number of columns used in Sheet1 displayed in a Message Box as follows:
So, ‘4’ is returned in the message box, and as we can see in Sheet1, the total number of columns in the used range is 4.
Example #4
Now, let’s say we wish to find the last used row and column number in Sheet2 of the file. Let us see what the Sheet2 contains:
To do this, we follow the below steps:
Code:
Code:
Code:
In the above code, xlCellTypeLastCell: represents the last cell in the used range.
Code:
So, we can see in the above screenshot that ‘12’ is returned in the message box, and as we can see in Sheet2, the last used row number is 12.
Similarly, if we wish to find the last used column number in Sheet2, we will follow the same steps as above except for a slight change in the code as follows:
Code:
Now when we run this code manually or by pressing F5, we get the last used column number in Sheet2 displayed in a Message Box as follows:
So, we can see in the above screenshot that ‘3’ is returned in the message box, and as we can see in Sheet2, the last used column number is 3.
Things to Remember About VBA UsedRange
Recommended Articles
This has been a guide to VBA UsedRange. Here we will show you how the UsedRange Property in a worksheet to find the used rows and columns in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –