VBA AutoFilter - filter with multiple Arrays’

Question

I’ve recorded the following macro below (short version)

Selection.AutoFilter
ActiveSheet.Range("$A$4:$FD$373").AutoFilter Field:=3, Criteria1:=Array( _
    "house", "flat", _
    "hotel"), Operator:=xlFilterValues

I want to add more string's (130 in total) but I keep getting the following message

''Too Many Line continuations''

The column has about 4-6 thousand rows with data, initially I wanted to delete rows with certain string values but that approach takes quite a long time to process.

I've looked everywhere and cant seem to get my head around it :(

Any ideas? Much appreciated

Gary's Student · Accepted Answer · 2014-10-26 16:43:04Z

1

Don't store the criteria in VBA

Put the 130 values in cells ZZ1 thru ZZ130

and then:

Sub marine()
    Dim ary(0 To 129) As Variant
    For i = 1 To 130
        ary(i - 1) = Range("ZZ" & i)
    Next i
    ActiveSheet.Range("$A$4:$FD$373").AutoFilter Field:=3, Criteria1:=ary, Operator:=xlFilterValues
End Sub

answered Oct 26, 2014 at 16:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xyz Over a year ago

Nice way of doing this Gary!

xyz · Accepted Answer · 2014-10-26 16:58:17Z

Here is a possibility

Make a tab call it FindReplace, in column A place your 130 strings in column B place # (in the 130 cells next to the 130 strings)

Change "MyTab" to the name of the tab with the with the 6000 rows

Change the .Columns("C:C") to the proper column

Run the macro this will change all the strings matching the 130 strings in the 6000 rows to #

Then use your macro on # to delete all unwanted rows

'MAKE A SHEET WITH 1ST COL = FIND 2ND COL = REPLACE
Sub FindReplace()
Dim FindValues As Variant
Dim ReplaceValues As Variant
Dim wsFR As Excel.Worksheet
Dim wsTarget As Excel.Worksheet
Dim lRow As Long
Dim i As Long

Sheets("FindReplace").Select

Set wsFR = ThisWorkbook.Worksheets("FindReplace")
Set wsTarget = ThisWorkbook.Worksheets("MyTab")
  lRow = wsFR.Range("A" & wsFR.Rows.Count).End(xlUp).Row

With wsTarget
    FindValues = wsFR.Range("A2:A" & lRow).Value
    ReplaceValues = wsFR.Range("B2:B" & lRow).Value

    For i = LBound(FindValues) To UBound(FindValues)
         .Columns("C:C").Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False
    Next i
End With
End Sub

Collectives™ on Stack Overflow

VBA AutoFilter - filter with multiple Arrays’

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related