Results 1 to 9 of 9

Macro to filter multiple values in a column

Threaded View

  1. #1
    Registered User
    Join Date
    05-08-2018
    Location
    Philippines
    MS-Off Ver
    MS Office 2010
    Posts
    6

    Macro to filter multiple values in a column

    Hi,

    I'm trying to create a macro that will filter multiple values using array. However, I'm not sure what is the problem on the code and it wont filter more than two values in array. Can anyone help me on this? I'm trying to filter column I if it contains "*Extra Air*", "*Extra-Air*", "*ExtraAir*", and if it does then I will tag it as Extra Air Con. The formula works for two criteria, but if i added another one, it wont work. Below is my sample code.

    Sub Categorized()
        Dim lastRow As Long
        Dim ws As Worksheet
        
        
        Set ws = Worksheets("Test")
        
        'find the last row
        lastRow = ws.Range("I" & Rows.Count).End(xlUp).Row
        'turn off autofilter if on
        If ws.AutoFilterMode = True Then
            ws.AutoFilterMode = False
        End If
        
        ApplicationScreenUpdating = False
        
        With ws
            'filter the column for "EXTRA AIRCON"
            .Range("I:I").AutoFilter Field:=1, Criteria1:=Array("*EXTRA AIR*", "*EXTRA-AIR*", "*EXTRAAIR*"), Operator:=xlFilterValues
            On Error Resume Next
            If lastRow > 2 Then
                .Range(.Range("C3"), .Range("C" & lastRow)). _
                   SpecialCells(xlCellTypeVisible).Value = "EXTRA AIR CON"
            End If
            .AutoFilterMode = False
    End Sub
    This has been cross-posted to mrexcel.com/forum/excel-questions/1064023-multiple-criteria-filter-vba.html"
    Last edited by jengesmeriz; 07-23-2018 at 09:31 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Chart - Want to filter a column with multiple values
    By Rudurk in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-23-2013, 09:45 AM
  2. [SOLVED] Edit Recorded Macro to filter column with about 200k rows based on a list of values.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-19-2012, 03:35 AM
  3. Macro to copy and paste special values for column data and filter column data
    By ascottbag in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-10-2012, 02:15 PM
  4. Advance filter unique values for multiple files macro
    By steeeeeve in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2011, 04:27 AM
  5. Macro to filter certain values in a column and delete the rest
    By willy91785 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2011, 12:23 PM
  6. filter for multiple column values
    By rgouette in forum Excel General
    Replies: 2
    Last Post: 03-10-2008, 02:08 PM
  7. [SOLVED] advanced filter macro to locate values via multiple criteria
    By jjfjr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2005, 11:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1