MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

bug in SpecialCells(xlCellTypeVisible).Address or is it just me?

  Asked By: Ashan    Date: Dec 15    Category: MS Office    Views: 1088

I wrote a macro that uses SpecialCells(xlCellTypeVisible).Address to
give me an array of rows in a worksheet that meet criteria in a
column autofilter. It works great for a limited number of responding
("visible") cells after an autofilter, but the address information
stops short of recording all row addresses when there is a larger
number of responsive cells.

My 2 questions:

1. Is there a limit to the number of row addresses that SpecialCells
(xlCellTypeVisible).Address can hold?

2. Is there a better way to code this than
var1 = ActiveSheet.Range("Sheet1!_FilterDatabase").SpecialCells
(xlCellTypeVisible).Address ?

Here's my macro, where rows with responsive cells are filled with
different colors after the autofilter:

'First filter, using Northwind database data -- everything works fine

Selection.AutoFilter Field:=2, Criteria1:="Chop-suey Chinese"
Selection.Interior.ColorIndex = 6

var1 = ActiveSheet.Range("Sheet1!_FilterDatabase").SpecialCells

Range("J1").Value = "Responding rows can be found at: " & var1

Selection.Interior.ColorIndex = 6 'colors responsive cells yellow
Selection.AutoFilter Field:=2 ' show "all" again after first
' filter to not exclude cells of
' next column to filter
'Second filter, values in
(xlCellTypeVisible).Address stop early at row 176 -- what gives?

Selection.AutoFilter Field:=3, Criteria1:="Fuller, Andrew"
Selection.Interior.ColorIndex = 3
var1 = ActiveSheet.Range("Sheet1!_FilterDatabase").SpecialCells
Range("J2").Value = "Responding rows can be found at: " & var1
MsgBox ("Address is " & var1)
Selection.Interior.ColorIndex = 3
Selection.AutoFilter ' turns autofilter off

The values for var1 on the first autofilter selection are:

Responding rows can be found at:

That's complete.

The values for var1 on the second autofilter selection are:

Responding rows can be found at:

This accounts for only about a third of the actual cells with "hits".

Commenting out the code for "pass 1" does not change the incomplete
results obtained in "pass 2."



No Answers Found. Be the First, To Post Answer.