UsedRange Property in VBA – Examples

In the previous article, we learned to select a range using VBA. Today we will learn about the UsedRange property in VBA. So let us start learning.

Used Range in Excel Spreadsheet

In order to understand the UsedRange property, we must know what is a used range in an excel spreadsheet. Used Range is particularly used when we wish to perform some action on all the used rows and columns of the excel spreadsheet.

Here are some shortcut keys associated with the used range of excel spreadsheets you are working with.

• Ctrl End keys – This shortcut is used to go to the cell formed by the intersection of the last column and the last row used by us.
• Ctrl Shift End keys – This shortcut helps to select the used range. The selects the used range between the active cell and the last used cell in the spreadsheet.

For instance, when we press the Ctrl and End keys together, this is the last used cell.

Select UsedRange using VBA

Now we will learn to use the UsedRange property in VBA. Suppose we have two spreadsheets named “Sheet1” and “Sheet2” where Sheet2 does not contain data. A1 containing S.no is the active cell.

Now, follow these steps to select the Used Range in Sheet1.

• Press Alt and F11 keys to open Visual Basic Editor in Excel. Go to the Insert tab and insert a new Module.
• Enter the following Sub Procedure code in the new module as follows.

Sub macro1()
'activate the Sheet1
Sheets("Sheet1").Activate
'selecting the used range of the active sheet
ActiveSheet.UsedRange.Select
End Sub

• Close the Visual Basic Editor. Press the Alt and F4 keys to open the list of available macros and choose macro1 from the list and run.

As a result, the property would select UsedRange A1:D10.

Copy and Paste Used Range

We would now copy and paste the above UsedRange A1:D10 in Sheet 1 to Sheet2. To do this, we will use the copy method of the UsedRange property.

UsedRange.Copy <paste_destination>

Now we will first activate Sheet1 and then copy the contents of the Used Range in Sheet1. We will specify the destination address as Sheets(“Sheet2”).Range(“A1”). We will only mention the upper left cell of the destination address in the destination range.

Sub macro2()
'activate sheet1
Sheets("Sheet1").Activate
'copy the used range from active sheet and paste it to Sheet2
ActiveSheet.UsedRange.Copy Sheets("Sheet2").Range("A1")
'activate sheet2 to see results
Sheets("Sheet2").Activate
End Sub

When you run the Sub Procedure, it gives us the required results.

Get the Address of the Used Range

We can get the address of UsedRange with the help of the Address Property of UsedRange. Let us say I wish to get the address of the Used Range in Sheet2, then we can use the following Sub Procedure.

Sub macro3()
'print the address of used range of Sheet1 using the address property
MsgBox "Address of Used Range is: " & Sheets("Sheet2").UsedRange.Address
End Sub

Therefore, when we run this Sub procedure, a message box appears showing the address of the used range in Sheet2.

Counting the Total Empty Cells in Used Range

There is no direct method or property in UsedRange property that would return the total number of empty cells so we need to write a Sub procedure that would go through each cell of used range and count it if it is an empty cell giving the total count at the end.

Sub macro4()
Dim myCell As Range
Dim myRange As Range
Dim count_empty As Integer
'myRange would contain the usedrange
Set myRange = Sheets("Sheet1").UsedRange
'iterating through each cell in usedrange
For Each myCell In myRange
'if current cell in used range is empty then
If (IsEmpty(myCell)) Then
'increment empty cell count by 1
count_empty = count_empty + 1
End If
Next myCell
'print the count of empty cells
MsgBox "Total number of empty cells in used range of Sheet 1 are:" & count_empty
End Sub

We have used the For Each loop to iterate through each cell of the Range myRange (used range).

Counting the Total Number of Rows and Columns in the Used Range

We can get the total number of used rows and columns of the used range using the property as follows.

UsedRange.Rows
UsedRange.Columns

This brings us to an end.

Thank you for reading.