Logo 
Search:

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: 1135
  

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:

Rows("1:1").Select
Selection.AutoFilter
'------------------------------------
'First filter, using Northwind database data -- everything works fine

Selection.AutoFilter Field:=2, Criteria1:="Chop-suey Chinese"
Range("B1:B795").Select
Selection.Interior.ColorIndex = 6

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

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

Range(var1).Select
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
'Range("Sheet1!_FilterDatabase").SpecialCells
(xlCellTypeVisible).Address stop early at row 176 -- what gives?

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

The values for var1 on the first autofilter selection are:

Responding rows can be found at:
$A$1:$I$1,$A$8:$I$8,$A$124:$I$124,$A$273:$I$273,$A$485:$I$485,$A$500:$
I$500,$A$720:$I$720,$A$783:$I$783,$A$795:$I$795

That's complete.

The values for var1 on the second autofilter selection are:

Responding rows can be found at:
$A$1:$I$1,$A$19:$I$19,$A$31:$I$31,$A$34:$I$34,$A$49:$I$49,$A$54:$I$54,
$A$61:$I$61,$A$66:$I$67,$A$81:$I$81,$A$93:$I$93,$A$99:$I$99,$A$122:$I$
122,$A$133:$I$133,$A$142:$I$142,$A$146:$I$146,$A$152:$I$152,$A$158:$I$
158,$A$161:$I$161,$A$168:$I$168,$A$176:$I$176

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."

Share: 

 

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