How to Use UsedRange in VBA (Examples)
Contents
Used Area of an Excel Sheet
There are many situations when we need to perform an action on all rows and columns with data in an Excel sheet. In order to do that, you need to know which row/column of data is the last. In other cases, we need to know the entire used area of a sheet (a combination of rows and columns with data). This area is also called a used range of an Excel sheet. This includes blank rows/columns — or even cells that are within the used range.
Selecting the Whole Used Range
To select the entire used range from a cell, SHIFT + CTRL + END keys can be pressed together after selecting a used cell.
Image of a Used Range
In this image, the highlighted areas are included in the used range though they are blank.
The areas highlighted in red are within the 13 rows and column I. Hence, those cells are included.
The cells highlighted in black were used initially though the data is now deleted. So, all columns up to the last used column and all rows up to the last used row are considered “USED.”
Selecting a Used Range by Row or by Column
SHIFT + CTRL + the arrow key (up/down/left/right) can select the used range by column/by row. But the drawback here is that this will not select the range up to the last used row/column. Instead, it will select the range until the cell just before a blank cell on its way.
For example, SHIFT + CTRL + down arrow from Cell B1 has selected the range of cells as in the image.
But repeated usage of the same keys i.e. pressing the combination of keys (SHIFT + CTRL + down arrow) several times can help in selecting the area until the last used row.
Find the Last Used Cell
To find the last used cell CTRL and END keys have to be used together. This will automatically select the cell which is the intersection of the last used row and last used column.
Used Range in VBA
It is now time to see how you can transfer this concept to VBA.
Here is a small piece of code that first selects a sheet and then highlights/selects the used range of that active sheet.
Sub usedrange_demo() Sheets("snackbar").Select ActiveSheet.UsedRange.Select End Sub
Output: Same as the first image in this article.
Find the Number of Used Rows in a Sheet
This piece of code can help to find the number of rows that are in the used range of a sheet.
Sub usedrange_demo() Dim rows Sheets("snackbar").Select rows = ActiveSheet.UsedRange.rows.Count MsgBox rows End Sub
Find the Number of Used Columns in an Active Sheet
Similar to the above code, here is another code snippet that counts the number of columns within the used range of a sheet and displays the result in a message box.
Sub usedrange_demo() Dim cols Sheets("snackbar").Select cols = ActiveSheet.UsedRange.Columns.Count MsgBox cols End Sub
Specialcells Method in VBA
SpecialCells is a built in function offered by VBA. It returns a range object which represents only specified types of cells.
Syntax:
RangeObject.SpecialCells (<Type> , <Value>)
Using The Specialcells Function Combined with Used Range to Find the Last Row
Sub usedrange_demo1() Dim lastrow Sheets("snackbar").Select lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row MsgBox lastrow End Sub
Using the Specialcells Function Combined with Used Range to Find the Last Column of Date
Sub usedrange_demo1() Dim lastcol Sheets("snackbar").Select lastcol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column MsgBox lastcol End Sub
Example: Calculate Simple Interest and Maturity Amount for All Rows of Data
In this program there is an Excel sheet with the principal amount, number of years, and age of the customer in different columns of the sheet. We need a program that does not use formulae. The simple interest and maturity amount should be calculated for each row but we do not know how many rows of data are there in the sheet.
Principal amount | No of yrs | Age of customer | Simple Interest | Maturity Amount |
10000 | 5 | 67 | ||
340600 | 6 | 45 | ||
457800 | 8 | 34 | ||
23400 | 3 | 54 | ||
12000 | 4 | 23 | ||
23545 | 4 | 56 | ||
345243 | 2 | 55 | ||
34543 | 3 | 24 | ||
23223 | 2 | 19 | ||
3656 | 1 | 65 |
So, here, used range property is used to fetch the last row number. Then you can use a for loop to iterate through each row, do the calculation, and enter the result in a different column of the same row. Age is a vital information in this example, since the rate of interest is different for senior citizens.
Sub simple_interest_calculation_rows() ' declare all required variables Dim Prin, no_of_years, cut_age, roi, simple_interest, mat_amt, lastrow ' find the last row of usedrange lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row ' iterate through each row in the used range For i = 2 To lastrow Prin = Cells(i, 1).Value no_of_years = Cells(i, 2).Value cut_age = Cells(i, 3).Value ' Set rate of interest depending on the cut_age of the customer ( varies for senior citizens ) If cut_age &amp;amp;gt; 59 Then ' senior citizens roi = 10 Else ' non- senior citizens roi = 8 End If ' Calculate the simple interest and maturity amount simple_interest = (Prin * no_of_years * roi) / 100 mat_amt = simple_interest + Prin ' Display the calculated output Cells(i, 4).Value = "The interest amount is " &amp;amp;amp; simple_interest Cells(i, 5).Value = "The maturity amount is " &amp;amp;amp; mat_amt Next End Sub
Output of the Program:
Principal amount | No of yrs | Age of customer | Simple Interest | Maturity Amount |
10000 | 5 | 67 | The interest amount is 5000 | The maturity amount is 15000 |
340600 | 6 | 45 | The interest amount is 163488 | The maturity amount is 504088 |
457800 | 8 | 34 | The interest amount is 292992 | The maturity amount is 750792 |
23400 | 3 | 54 | The interest amount is 5616 | The maturity amount is 29016 |
12000 | 4 | 23 | The interest amount is 3840 | The maturity amount is 15840 |
23545 | 4 | 56 | The interest amount is 7534.4 | The maturity amount is 31079.4 |
345243 | 2 | 55 | The interest amount is 55238.88 | The maturity amount is 400481.88 |
34543 | 3 | 24 | The interest amount is 8290.32 | The maturity amount is 42833.32 |
23223 | 2 | 19 | The interest amount is 3715.68 | The maturity amount is 26938.68 |
3656 | 1 | 65 | The interest amount is 365.6 | The maturity amount is 4021.6 |
Conclusion:
Some Points to Remember About the UsedRange in VBA.
- UsedRange is the used area of an Excel sheet.
- VBA UsedRange includes all cells that currently have data or some formatting .(colors/borders/shading)/earlier had data/cell format.
- The last row/column of data or the number of columns/rows in a used range of a sheet can be found using the UsedRange property in VBA.
- It is not mandatory that the top left cell be a part of the defined UsedRange.
- Selected cells are active cells and are not used cells unless they contain/contained some data/formatting.