Filter and count hidden cells problem

[Otto Moehrbach]

Excel XP & Win XP
I have headers in row 2.
I have data in row 3 only, therefore, RngToCopy is B3. This is correct.
I invoke an Auto-Filter on the data and, as a result, there are NO visible
rows (below the header row).
I have the following 2 message boxes:
MsgBox RngToCopy.Address(0, 0)
MsgBox RngToCopy.SpecialCells(xlCellTypeVisible).Count
The first MsgBox gives B3. Correct.
The second MsgBox gives 16776960 which is very close to the number of cells
in the sheet!!.
Since B3 is the RngToCopy and B3 is hidden (by filter), I expected the
second MsgBox to give a zero.
How can I get the count of visible cells in a range if all the cells in the
range are hidden?
Or, to put it another way, how can I determine that no rows satisfied the
filter criteria?
Thanks for your time. Otto

 

[Dave Peterson]

Try this on a test worksheet:

Select A1:E20
type asdf
and hit ctrl-enter to fill those 100 cells
select B3
Hide row 3
Edit|goto|special|visible cells only

What's selected?

The same thing happens in code.

Using .specialcells with a single cell can give you unwanted results.

And the same kind of thing can happen when you do Edit|replace. If only one
cell is selected, the whole sheet (visible cells only) are affected.

So you'll want to make sure that the number of rows visible after the filter is
bigger than 0.

One way:

With activesheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

 

[Otto Moehrbach]

Dave
Thanks for taking the time to explain that. I understand now what was
happening. I'll incorporate your code to test for visible rows including
the headers and will bypass the meat of the code if it's only 1. Thanks
again. Otto