In VBA, the UsedRange property represents the range in a worksheet that has data in it. The usedrange starts from the first cell in the worksheet where you have value to the last cell where you have value. Just like the following example where you have used range from A1 to C11.
Note: UsedRange property is a read-only property.
Write a Code with UsedRange
Use the following code.
- First, you need to specify the worksheet.
- Then enter a dot (.) and enter “UsedRange”.
- After that, use the property or method that you want to use.
- In the end, run the code.
Sub vba_used_range() ActiveSheet.UsedRange.Clear End Sub
The above code clears everything from the used range from the active sheet.
Use the following code to copy the entire UsedRange.
Sub vba_used_range() ActiveSheet.UsedRange.Copy End Sub
Count Rows and Columns in the UsedRange
There’s a count property that you can use to count rows and columns from the used range.
MsgBox ActiveSheet.UsedRange.Rows.Count MsgBox ActiveSheet.UsedRange.Columns.Count
The above two lines of code shows a message box with the count of rows and columns that you have in the used range.
Activate the Last Cell from the UsedRange
You can also activate the last cell from the used range (that would be the last used cell in the worksheet). Consider the following code.
Sub vba_used_range() Dim iCol As Long Dim iRow As Long iRow = ActiveSheet.UsedRange.Rows.Count iCol = ActiveSheet.UsedRange.Columns.Count ActiveSheet.UsedRange.Select Selection.Cells(iRow, iCol).Select End Sub
This code takes the rows and columns count using the UsedRange property and then use those counts to select the last cell from the used range.
Refer to UsedRange in a Different Worksheet
If you are trying to refer to the used range in a worksheet other than the active sheet then VBA will show an error like the following.
So, the worksheet where you are referring must be activated (only then you can use the UsedRange property).
Sub vba_used_range() Worksheets("Sheet4").Activate Worksheets("Sheet4").UsedRange.Select End Sub
That means you can’t refer to the used range in a workbook that is closed. But you can use open a workbook first and then, activate the worksheet to use the UsedRange property.
Get the Address of the UsedRange
Use the following line of code to get the address of the used range.
Sub vba_used_range() MsgBox ActiveSheet.UsedRange.Address End Sub
Count Empty Cells from the Used Range
The following code uses the FOR LOOPS (For Each) and loops through all the cells in the used range and counts cells that are empty.
Sub vba_used_range() Dim iCell As Range Dim iRange As Range Dim c As Long Dim i As Long Set iRange = ActiveSheet.UsedRange For Each iCell In ActiveSheet.UsedRange c = c + 1 If IsEmpty(iCell) = True Then i = i + 1 End If Next iCell MsgBox "There are total " & c & _ " cell(s) in the range, and out of those " & _ i & " cell(s) are empty." End Sub
When you run this code, it shows a message box with the total count of cells and how many cells are empty out of that.
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells in VBA
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using a VBA Code
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes