Autofilter / Hide rows of cells not containing words from array

user_13724601 0
Viewed 235

There are 2 sheets in my Excel workbook: Sheet1: MUFG Client Sheet 2: Company Information

So basically I want to do autofilter at MUFG Client sheet in "Keyword" field (Field 29) from another cell (I18) in Company Information sheet. And the content of the cell is the result from vlookup formula so it will change and not always be the same. Here goes my vba code:

Sub filter_by_cell_value ()
    Sheets("MUFG Client").Range("A2").Autofilter Field:=29, _
    Criteria1:="=Asterixsymbol" & Sheets("Company Information").Cells(18,6).Value & "*", xlOperator:= xlOr
End Sub

My objective is I want the autofilter can read the text in cell I18 without specific text/criteria.

For example, if the cell I18 contains Cosmetics, Chemical --> I want the autofilter in Keyword Field can show the word Cosmetics or Chemical, then

If I change the content of company information sheet into different company (The result of vlookup), the cell I18 in Company Information will change into Food & Beverage, Business Expansion, FMCG--> And I also want the autofilter in the keyword Field (MUFG Client sheet) shows Food & Beverage or Business Expansion or FMCG (Autofiltering contains those words by ignoring order)

And from my vba code above, Cells(18,6) is cell I18 in Company Information Sheet.

Is it possible to do so? I think I have to discuss this directly to make you guys understand. Sorry if this makes misunderstanding.

Thank you so much...

autofilter cell criteria vba excel
1 Answers

If you want this to run automatically on Cells(18, 6) (BTW it is F18 and not I18. Changed it below to make I18) change, then use Calculate event.

if the cell I18 contains "Cosmetics, Chemical" then the filter should be "Cosmetics" or "Chemical" ? Try following

Sub filter_by_cell_value()

arr = Split(Sheets("Company Information").Cells(18, 9).Value, ",")

Sheets("MUFG Client").Range("A2").AutoFilter Field:=29, Criteria1:=arr, Operator:=xlFilterValues

End Sub

EDIT As per comments below, we can hide rows with the following procedure.

Sub hide_Rows_by_cell_value()
Dim wb As Workbook, CompInfo As Worksheet, MufgClient As Worksheet
Dim srcCl As Range, lr As Long, FltCol As Range, cl As Range, hideRng As Range
Set wb = ThisWorkbook
Set CompInfo = wb.Sheets("Company Information")
Set MufgClient = wb.Sheets("MUFG Client")

Set srcCl = CompInfo.Cells(18, 9)
arr = Split(srcCl.Value, ",")

lr = MufgClient.Range("AC" & MufgClient.Rows.Count).End(xlUp).Row
Set FltCol = MufgClient.Range("AC3:AC" & lr) '2nd Row contains table headers

For Each cl In FltCol
    chk = 0
    For i = 0 To UBound(arr)
    chk = chk + InStr(1, cl.Value, Trim(arr(i)), vbTextCompare)
    Next
    If chk = 0 Then
        If hideRng Is Nothing Then
        Set hideRng = cl
        Else
        Set hideRng = Union(hideRng, cl)
        End If
    End If
Next

hideRng.EntireRow.Hidden = True

End Sub
user_98080630
Related