Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting data from sheet to sheet | Excel Discussion (Misc queries) | |||
counting cells that are 0 in a range of non-contiguous cells | Excel Worksheet Functions | |||
counting like items on one sheet, showing totals on another sheet | Excel Programming | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions |