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.
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.
This brings us to an end.
Thank you for reading.