Hello,
I have a code to populate visible cells which works fine on its own (eg. If I auto-filter manually & then run it) but its not working within this code I wrote to automate my filtering & I don't know why.
Code is below.The De-Bugger highlighted the line "Set r2 = r1.SpecialCells(xlCellTypeConstants, 23)" and said that No Cells Found. But on the spreadsheet the Macro has successfully filtered for those cells and there are cells waiting to be populated. On my first filter (=4), all cells in column E are Blank - I don't know whether that has anything to do with it, but the Labelling code was written to handle both blank & non-blank cells.
The structure I was trying to put together is Insert Row(once only)/Auto-Filter1/Label1/Undo-FIlter1/Auto-Filter2/Label2.
My searching so far has not found the solution to why this code does not work - would greatly appreciate someone's advice. I am learning alot through this process.
Sub FIlter_Label()
'
' FIlter_Label Macro
'
'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.LargeScroll ToRight:=1
ActiveSheet.Range("$A$1:$DS$14914").AutoFilter Field:=35, Criteria1:="4"
Dim r1 As Range
Dim r2 As Range, a As Range
Dim r3 As Range
Set r1 = Range("E2:E14914").SpecialCells(xlCellTypeVisible)
Set r2 = r1.SpecialCells(xlCellTypeConstants, 23)
Set r3 = r1.SpecialCells(xlCellTypeBlanks)
For Each a In r2.Cells
a = a & "//Pattern 1"
Next a
r3 = "Pattern 1"
Rows("1:1").Select
Range("S1").Activate
Selection.AutoFilter
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=1
Range("AI1").Select
ActiveSheet.Range("$A$1:$DR$14914").AutoFilter Field:=35, Criteria1:="5"
Dim s1 As Range
Dim s2 As Range, b As Range
Dim s3 As Range
Set s1 = Range("E2:E14914").SpecialCells(xlCellTypeVisible)
Set s2 = s1.SpecialCells(xlCellTypeConstants, 23)
Set s3 = s1.SpecialCells(xlCellTypeBlanks)
For Each b In s2.Cells
b = b & "//Pattern 2"
Next b
s3 = "Pattern 2"
Rows("1:1").Select
Range("S1").Activate
Selection.AutoFilter
Selection.AutoFilter
End Sub
Thanks,
Trent
Bookmarks