Changing data returned from an autofilter
 

Hi,

I am working on code that takes my list, filters first on
column a, changes the data in column a from one thing to
another depending on what the autofilter returns, puts on
another filter in the same column with different criteria,
changes those, and so on.
My problem is that if there is a zero count on the data
returned, the text is copied across the entire spreadsheet
(a1:iv1) (don't know if this is relevant or not, but when
the zero count is returned, i don't see any of the
spreadsheet except for my headers - the rest of it is that
nether region beyond row 65536)...
I've exhausted my brain trying to figure this out.
Below is a snippet of the code that is causing me a
problem:

Selection.AutoFilter Field:=1, Criteria1:="=E*",
Operator:=xlOr, Criteria2:="=D*"
Selection.AutoFilter Field:=2, Criteria1:="S",
Operator:=xlOr, Criteria2:="E"
Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells
(xlCellTypeVisible).Value = "UNDER INVESTIGATION"
ActiveSheet.ShowAllData
Range("A1").Offset(1, 0).Select


Does anybody have any suggestions? I hope I've explained
this clearly...
Thanks,
Chris

Changing data returned from an autofilter
 

Dim rng as Range, rng1 as Range
set rng = Range(ActiveCell,
ActiveCell.End(xlDown)).SpecialCells(xlCellTypeVis ible).Value
set rng1 = rng.Areas(rng.Areas.count)
if rng1.rows(rng1.rows.count).Row < 65536

rng.Value = "UNDER INVESTIGATION"


or another might be
Dim rng as Range, rng1 as Range
set rng1 = Nothing
set rng = ActiveSheet.Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count - 1)
set rng = intersect(rng,Activecell.EntireColumn)
on error resume next
set rng1 = rng.SpecialCells(xlVisible)
On error goto 0
if not rng1 is nothing then
rng1.Value = "UNDER INVESTIGATION"
End if



--
Regards,
Tom Ogilvy


"ChrisBat" wrote in message
...
Hi,

I am working on code that takes my list, filters first on
column a, changes the data in column a from one thing to
another depending on what the autofilter returns, puts on
another filter in the same column with different criteria,
changes those, and so on.
My problem is that if there is a zero count on the data
returned, the text is copied across the entire spreadsheet
(a1:iv1) (don't know if this is relevant or not, but when
the zero count is returned, i don't see any of the
spreadsheet except for my headers - the rest of it is that
nether region beyond row 65536)...
I've exhausted my brain trying to figure this out.
Below is a snippet of the code that is causing me a
problem:

Selection.AutoFilter Field:=1, Criteria1:="=E*",
Operator:=xlOr, Criteria2:="=D*"
Selection.AutoFilter Field:=2, Criteria1:="S",
Operator:=xlOr, Criteria2:="E"
Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells
(xlCellTypeVisible).Value = "UNDER INVESTIGATION"
ActiveSheet.ShowAllData
Range("A1").Offset(1, 0).Select


Does anybody have any suggestions? I hope I've explained
this clearly...
Thanks,
Chris