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.
The above code clears everything from the used range from the active sheet.
Use the following code to copy the entire UsedRange.
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.
The above two lines of code show 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.
Dim iCol As Long
Dim iRow As Long
iRow = ActiveSheet.UsedRange.Rows.Count
iCol = ActiveSheet.UsedRange.Columns.Count
This code takes the rows and columns count using the UsedRange property and then uses 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 to which you are referring must be activated (only then you can use the UsedRange property).
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.
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.
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
MsgBox "There are total " & c & _
" cell(s) in the range, and out of those " & _
i & " cell(s) are empty."
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
- 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 VBA
- 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