Number of rows/columns occupied

[Will]

Is there a function which counts the number of
rows / columns in an Excel worksheet
which contain something (i.e. are not empty)?

 

[Billy Liddel]

=CountA(A:A)
=CountA(1:1)

 

[Mike H]

Hi,

Alt+F11 to open vb editor, right click 'This Workbook' and insert module and
paste these 2 UDF's below in

call with

=usedrows()
=usedcolumns()

Function UsedRows() As Long
UsedRows = ActiveSheet.UsedRange.Rows.Count
End Function

Function UsedColumns() As Long
UsedColumns = ActiveSheet.UsedRange.Columns.Count
End Function

Mike

 

[Rick Rothstein]

Those functions will count blank rows and/or blank columns that are located
within the UserRange (plus it is possible for the UsedRange to contain blank
rows and/or blank columns after the last piece of data in a row or column).

 

[Shane Devenshire]

Hi,

As stated the answer is no. You have asked for A function which counts all
the occupied rows and columns in a worksheet. There is no function that will
do both at once, rows and columns. Also, there is no built in function which
will calculate the number of occupied rows in all columns at once.

What you could do is insert a new column A and a new row in position 1,
enter the formula =COUNTA(B2:IV2) in A2 and copy it down as far as you want
65536 or 1,048,576 (2007). Then in A1 enter the formula =SUM(A2:A65536)

This will count the number of rows that have anything on them. You can do
something similar for columns.

Also, none of these formulas consider whether the cells are formatted if
that is important.