Changing Data returned from autofilter

[ChrisBat]

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 o
what the autofilter returns, puts on another filter in the same colum
with different criteria, changes those, and so on.
My problem is that if there is a zero count on the data returned, th
text is copied across the entire spreadsheet (A1:IV1). (don't know i
this is relevant, but when the 0 count is returned, i don't see any o
the spreadsheet except for my headers - the rest of it is that nethe
region beyond row 65,536).
I've exhausted my brain trying to figure this out.
Below is a snippet of the code that is causing me a problem:

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="6"
Selection.Offset(1, 0).Select
Range(ActiveCell
ActiveCell.End(xlDown)).SpecialCells(xlCellTypeVisible).Value
"PENDING PITS"
ActiveSheet.ShowAllData

Does anybody have any suggestions? I hope I've explained thi
clearly....
Thanks,
Chris
Range("A1").Offset(1, 0).Selec

 

[Debra Dalgleish]

You can use code similar to the following:

'====================
Sub FillDownFilter()
Dim rng As Range
Dim rng2 As Range

Worksheets("Sheet1").Range("A1").AutoFilter _
Field:=2, Criteria1:=6

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No records selected"
Else
Set rng = ActiveSheet.AutoFilter.Range
Range(rng(1, 1), rng(1, 1).End(xlDown)) _
.SpecialCells(xlCellTypeVisible).Value = "PENDING PITS2"
End If
ActiveSheet.ShowAllData
End Sub
'===========================