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

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.