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



baga wrote:

Hi, it is possible to get count of non-empty rows and columns in worksheet? I
cant find any property in Excel.Worksheet. Reading all cells and checking
value not null or empty very slow.

--

Dave Peterson