Counting used cells on sheet

[Slim Slender]

When I run the following in VBA, it puts the value 350 in Sheet40,
Range A1.

Worksheets("Sheet40").Range("A1") = Sheets("Sheet1").UsedRange.Count

This is because there are 5 columns of figures and Column A, the
longest one, has 70 items.
The problem is that some of the columns are not as long as A, do not
have as many items, aren't
filled all the way to the bottom. However, when I click on the sheet
selector, the little square in the
top left corner of the sheet, "Count: 126" appears in the status bar
at the bottom of the sheet.
This is the correct number of elements on the sheet. How can I capture
this number with VBA.
Also, I want to step through a variable number of sheets and get this
count for each one and list
those counts on a separate sheet.

 

[James Ravenswood]

When I run the following in VBA, it puts the value 350 in Sheet40,
Range A1.

Worksheets("Sheet40").Range("A1") = Sheets("Sheet1").UsedRange.Count

This is because there are 5 columns of figures and Column A, the
longest one, has 70 items.
The problem is that some of the columns are not as long as A, do not
have as many items, aren't
filled all the way to the bottom. However, when I click on the sheet
selector, the little square in the
top left corner of the sheet, "Count: 126" appears in the status bar
at the bottom of the sheet.
This is the correct number of elements on the sheet. How can I capture
this number with VBA.
Also, I want to step through a variable number of sheets and get this
count for each one and list
those counts on a separate sheet.

Sub dural()
Dim n As Long
n = Application.WorksheetFunction.CountA(Sheets("Sheet1").UsedRange)
Worksheets("Sheet40").Range("A1") = n
End Sub

 

[Rick Rothstein]

**IF** your cells contain only constants, no formulas, then you can use
this...

Worksheets("Sheet40").Range("A1") = _
Cells.SpecialCells(xlCellTypeConstants).Count

If, on the other hand, you have formulas and/or constants in your cells, and
some of those formulas are displaying the empty string ("") but you still
need to count them, then you can use this...

Worksheets("Sheet40").Range("A1") = _
Cells.SpecialCells(xlCellTypeConstants).Count + _
Cells.SpecialCells(xlCellTypeFormulas).Count

Rick Rothstein (MVP - Excel)




"Slim Slender" wrote in message

When I run the following in VBA, it puts the value 350 in Sheet40,
Range A1.

Worksheets("Sheet40").Range("A1") = Sheets("Sheet1").UsedRange.Count

This is because there are 5 columns of figures and Column A, the
longest one, has 70 items.
The problem is that some of the columns are not as long as A, do not
have as many items, aren't
filled all the way to the bottom. However, when I click on the sheet
selector, the little square in the
top left corner of the sheet, "Count: 126" appears in the status bar
at the bottom of the sheet.
This is the correct number of elements on the sheet. How can I capture
this number with VBA.
Also, I want to step through a variable number of sheets and get this
count for each one and list
those counts on a separate sheet.

 

[Slim Slender]

Thanks for both responses.

The one that works best for me is: