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

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

VBA (or Excel) crashes if Count of Visible Rows in Autofilter Rang
 

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(xlCellTypeV isible))


--
Regards,
Tom Ogilvy

" wrote:

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

VBA (or Excel) crashes if Count of Visible Rows in Autofilter Rang
 

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

Tom Ogilvy wrote:

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(xlCellTypeV isible))