2007


I have this challenge:

Set CurrRng = wks.Range(Cells(DataStartRow, DataTopCol).Address, _
Cells(wks.Cells(Rows.Count, "B").End(xlUp).Row, _
DataTopCol)).SpecialCells(xlCellTypeVisible)l


The code above yields: (via CurrRng.address)

If CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count = 1 (one data item row)
Then $1:$67,$149:$149,$332:$65536

If CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count 1 (data item rows)
Then $149

The only data row visible either way is 149

The VBA code, after the "SET," crashes with $1:$67,$149:$149,$332:$65536
but is OK with $149

My current workaround is:

DataRowsCount = CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count
Set CurrRng = wks.Range(Cells(DataStartRow + IIf(DataRowsCount = 1, 1, 0), _
DataTopCol).Address, Cells(wks.Cells(Rows.Count, "B").End(xlUp).Row, _
DataTopCol)).SpecialCells(xlCellTypeVisible)

What causes the different results?

What is the best way to avoid the issue?

I really appreciate any thoughts.

EagleOne