Each worksheet has a .usedrange property.
But that doesn't mean that it accurately represents the non-empty rows and
columns.
If you put a value in E5 and another value in L20, you would see 16 rows and 8
columns with something like:
With ActiveSheet.UsedRange
MsgBox .Address & vbLf & .Rows.Count & vbLf & .Columns.Count
End With
Even though only 2 rows and two columns are non-empty.
And if you use a cell and then clear that cell, excel doesn't automatically
readjust that usedrange to what you would want.
Debra Dalgleish does share some techniques for resetting that usedrange:
http://contextures.com/xlfaqApp.html#Unused
If you really wanted to know the number of non-empty rows and non-empty
columns...
Option Explicit
Sub testme()
Dim RngConst As Range
Dim RngForm As Range
Dim RngBoth As Range
With ActiveSheet.UsedRange
Set RngConst = Nothing
Set RngForm = Nothing
On Error Resume Next
Set RngConst = .Cells.SpecialCells(xlCellTypeConstants)
Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas)
On Error Resume Next
If RngConst Is Nothing Then
Set RngBoth = RngForm
ElseIf RngForm Is Nothing Then
Set RngBoth = RngConst
Else
Set RngBoth = Union(RngConst, RngForm)
End If
If RngBoth Is Nothing Then
MsgBox "no used rows or columns!"
Else
MsgBox "Rows: " & Intersect(RngBoth.EntireRow, _
.Columns(1)).Cells.Count _
& vbLf & _
"Cols: " & Intersect(RngBoth.EntireColumn, .Rows(1)).Cells.Count
End If
End With
End Sub
