Counting used cells on sheet
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. |
Counting used cells on sheet
On Feb 20, 9:49*am, Slim Slender wrote:
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("Sheet 1").UsedRange) Worksheets("Sheet40").Range("A1") = n End Sub |
Counting used cells on sheet
**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. |
Counting used cells on sheet
Thanks for both responses.
The one that works best for me is: Worksheets("Sheet40").Range("A1") = _ * * * * * *Cells.SpecialCells(xlCellTypeConstants).Count + _ * * * * * *Cells.SpecialCells(xlCellTypeFormulas).Count Rick Rothstein (MVP - Excel) |
All times are GMT +1. The time now is 09:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com