Auto Filter VBA help?

[MichaelRSnow]

Hi All

I recored via key strokes a macro, which I wanted to filter a report and change some values, code below. It doesn't get past the creation of the auto filter without debug - select method of range class failed?

Can you help

'Filter the "Change Maximum Loan £ column (X) to 5,000,000" and leave the switchers as 5,000,000 and change all the others to 9,999,999.'
Sheets("Sheet1").Rows("1:1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Selection.AutoFilter Field:=24, Criteria1:="5,000,000"
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
Selection.AutoFilter Field:=8, Criteria1:="<>*SWI*", Operator:=xlAnd
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Columns("X:X").Select
Selection.Replace What:="5000000", Replacement:="9999999", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.AutoFilter Field:=24
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Selection.AutoFilter Field:=8
Rows("1:1").Select
Range("E1").Activate
Selection.AutoFilter

 

[wigi]

You probably meant something like this - untested though:

'Filter the "Change Maximum Loan £ column (X) to 5,000,000" and leave the switchers as 5,000,000 and change all the others to 9,999,999.'
With Sheets("Sheet1").UsedRange
    .AutoFilter Field:=24, Criteria1:="5,000,000"
    .AutoFilter Field:=8, Criteria1:="<>*SWI*"
    .Columns("X:X").SpecialCells(xlCellTypeVisible).Replace What:="5000000", Replacement:="9999999", LookAt:=xlPart
    .AutoFilter
End With

Please wrap your code in

 tags to show them in a decent way here.

 

[MichaelRSnow]

Brilliant news, works really well. Thank you