Learn how to count visible cells, for example, count the number of visible rows when you filter data. Several methods are explained – not all of them work!
Special Cells
There is a useful method in Excel which takes a range object (i.e. a group of cells on a worksheet) and then it selects specific cells within this range object, depending on the type you specify. The syntax of the command is:
RangeObject.SpecialCells(CellType)
For example, the range object might be a named range (e.g. ReportRange) or all the cells on the current sheet (ActiveSheet.Cells). The CellType can be one of many different values. For example:
xlCellTypeBlanks (empty cells)
xlCellTypeComments (cells containing comments)
xlCellTypeAllValidation (cells having validation criteria)
xlCellTypeLastCell (the last cell in the used range)
xlCellTypeVisible (all visible cells)
It is this last option, all visible cells, that is of interest to us today.
When these visible cells are selected from filtered data, the result is a collection of non-overlapping ranges, split apart by hidden rows and/or columns. This turns out to be quite useful to understand because it explains why the “expected” methods of counting don’t work in this situation.
Ways not to Count Visible Cells
Normally you can count the number of rows in a range by adding .rows.count onto the end of it. For example, to count the number of rows in the current region (the region bounded by empty rows and columns), you could use the following code, assuming that the currently selected cell is in the region of interest:
ActiveCell.CurrentRegion.Rows.Count
However, if you apply this approach to visible cells you don’t get the result you were hoping for.
ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count
This only counts the number of rows in the very first area within this collection of non-overlapping areas. If your first row of data is hidden, then all you are going to count is your header row.
One way to get around this is to create a loop going through all of the areas in the visible cells range and adding up all the rows individually. For example:
Ct = 0 ' Ct = Row number count For Each Ar In ReportRange.SpecialCells(xlCellTypeVisible).Areas Ct = Ct + Ar.Rows.Count Next
This does work if you have no hidden columns. Hidden columns would break up the range into more areas and you could very well end up counting the same row two or more times.
Ways to Count Visible Cells
If you want to count the number of rows within a filtered range, then the best option is to try to restrict your range to a single column and then count all the cells in that range.
For example, the following piece of code counts all visible cells in the current region:
ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Count
There are two ways that you can select a single column.
The first way is to define the range by specifying the first and last cell within the Range object. For example, assuming that the first column is in your range and is not completely hidden:
Range(Cells(StartRow,1),Cells(EndRow,1)).SpecialCells(xlCellTypeVisible).Count
Note: The Cells command has two arguments: a row number and then a column number.
The second way is to resize the range to only one column wide using the Resize property. The Resize property has two optional arguments: the row size and the column size. The resizing is done relative to the cell in the top left hand corner of your range. Just make sure that this column is not hidden!
ActiveCell.CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count
If the first column is hidden but your second column isn’t, then try moving your starting point for the resize property over one column using the Offset property.
ActiveCell.CurrentRegion.Offset(0,1).Resize(, 1).SpecialCells(xlCellTypeVisible).Count
If you want to apply this Resize property to selecting one row only then try this:
ActiveCell.CurrentRegion.Resize(1).SpecialCells(xlCellTypeVisible).Count
I hope that this not only saves you time, but also the frustrations and headaches in debugging your code!