VBA (or Excel) crashes if Count of Visible Rows in Autofilter Range = 1

[EagleOne]

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).Rows.Count = 1 (one data item row)
Then >>>> $1:$67,$149:$149,$332:$65536

If CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).Rows.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).Rows.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

 

[Tom Ogilvy]

do it manually - you will see that when only one cell is selected, then the
special cells command works on the entiresheet.


Possible work around:

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

Set CurrRng = Intersect(CurrRng,CurrRng.SpecialCells(xlCellTypeVisible))

 

[EagleOne]

Tom, I replied via Google but I do not yet see it via Agent