Home ➜ VBA Tutorial ➜ How to use UsedRange Property in VBA in Excel
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, 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.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Copy UsedRange
Use the following code to copy the entier 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 form 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 how an error like the following.
So, the worksheet where you are referring to must be activate (only then you can use the UsedRange property).
Sub vba_used_range()
Worksheets("Sheet4").Activate
Worksheets("Sheet4").UsedRange.Select
End Sub
That mean 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 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.
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
- How to Select a Range/Cell using VBA in Excel