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

E

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
 
T

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top