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.”

Blank areas included in a used range.

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.

Using SHIFT + CTRL + arrow key to select rows until blank row.

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.

Continuously pressing SHIFT + CTRL + arrow key to highlight all rows until last 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.

using CTRL and END to 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
Result of code used to find the number of rows that are in the used range of a sheet.

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
Result of code used to count the number of columns within the used range of a sheet and display the same in a message box.

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 specialcells function combined with used range to find the last row.

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
Using specialcells function combined with used range to find the last column.

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 amountNo of yrsAge of customerSimple InterestMaturity Amount
10000567  
340600645  
457800834  
23400354  
12000423  
23545456  
345243255  
34543324  
23223219  
3656165  

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;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;amp; simple_interest 
    Cells(i, 5).Value = "The maturity amount is " &amp;amp;amp;amp; mat_amt
Next

End Sub

Output of the Program:

Principal amountNo of yrsAge of customerSimple InterestMaturity Amount
10000567The interest amount is 5000The maturity amount is 15000
340600645The interest amount is 163488The maturity amount is 504088
457800834The interest amount is 292992The maturity amount is 750792
23400354The interest amount is 5616The maturity amount is 29016
12000423The interest amount is 3840The maturity amount is 15840
23545456The interest amount is 7534.4The maturity amount is 31079.4
345243255The interest amount is 55238.88The maturity amount is 400481.88
34543324The interest amount is 8290.32The maturity amount is 42833.32
23223219The interest amount is 3715.68The maturity amount is 26938.68
3656165The interest amount is 365.6The maturity amount is 4021.6

Conclusion:

Some Points to Remember About the UsedRange in VBA.

  1. UsedRange is the used area of an Excel sheet.
  2. VBA UsedRange includes all cells that currently have data or some formatting .(colors/borders/shading)/earlier had data/cell format.
  3. 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.
  4. It is not mandatory that the top left cell be a part of the defined UsedRange.
  5. Selected cells are active cells and are not used cells unless they contain/contained some data/formatting.

Leave a Reply

Your email address will not be published. Required fields are marked *