Tom,

I'll also post the following as a new item is case you will not be available this weekend.

Ran into 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: (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 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?

I really appreciate your thoughts and time

EagleOne

Tom Ogilvy wrote:

Yes you did, but if you know you will have an autofilter range that contains
the last used row in your sheet, then your one liner will be sufficient
whether the sheet is in filtermode or not. (rows hidden by the filter or not)