Excel VBA Filter out table rows depending on cell value

Question

I'm trying to exclude values from a table depending on values.

I've got the following table which has columns with names, customer ID, customer city and total spent:

And I would like to filter out the two following specific values, which are customer ID's.

So that the end table would look like this, notice it no longer has Liam Gallagher or Tom Johnson.

I know how to filter these out manually but I was wondering how I can do it on VBA?

I've scoured the internet and I haven't been able to find any solutions. Also the two tables will have different arrays as the list of customers can change as well as the customer IDs, so if it's possible to make it dynamic that would be great.

Thanks

Yes absolutely, but there are three areas where I'm blocked. Firstly, when I do this manually I insert a =count(search(Column F table array, reference to customer ID). Secondly I need to make the Column F table array dynamic and thirdly I also need to make the reference to the table I'd like to filter dynamic too. — Bikat Uprety, Feb 25 '22 at 15:05
Ok. So maybe you should narrow your question to just *one* of those issues and show the code you have so far. — BigBen, Feb 25 '22 at 15:06

simple-solution · Answer 1 · 2022-02-26T16:55:50.700

Sub advanced_filter()
    Dim rgData As Range, rgCriteria As Range, rgOutput As Range
    
    With ThisWorkbook.Worksheets("Sheet 1")
        Set rgData = .Range("A1").CurrentRegion
        Set rgCriteria = .Range("F1").CurrentRegion
        Set rgOutput = .Range("I1")
    
        .Range("I1:L7").ClearContents
    End With
    rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

End Sub

UPDATE:
Is there anyway that the customerID's in column F and G
can be stacked vertically instead of horizontally?

You can do this "manually" with vba ;-)

Sub advanced_filter()
    Dim rgData As Range, rgCriteria As Range, rgOutput As Range
    
    With ThisWorkbook.Worksheets("Sheet 1")
        Set rgData = .Range("A1").CurrentRegion
        Call CopyTranspose
        Set rgCriteria = .Range("A10").CurrentRegion
        Set rgOutput = .Range("H1")
    
        .Range("H1:L7").ClearContents
    End With
    rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

End Sub

Sub CopyTranspose()
'
' CopyTranspose Macro
'
    Range("A10:A15").EntireRow.ClearContents

    Dim lineCount As Integer
    lineCount = Range("F1").CurrentRegion.Count
    
    'Transpose Copy
    transposeAndPasteCol Range("F1").CurrentRegion, Range("A11")
    
    Set LastCellRange = Range("B10").Offset(0, lineCount - 2)
    Range("F1").Copy Range(Range("B10"), LastCellRange)

    Range("A10:A20").Delete Shift:=xlToLeft
End Sub

Sub transposeAndPasteCol(ColToCopy As Range, pasteRowTarget As Range)
    pasteRowTarget.Resize(, ColToCopy.Rows.Count) = Application.WorksheetFunction.Transpose(ColToCopy.Value)
End Sub

UPDATE 2:

Sub advanced_filter_V4()
    Dim rgData As Range, rgCriteria As Range, rgOutput As Range
    
    With ThisWorkbook.Worksheets("Sheet 1")
        Set rgData = .Range("A1").CurrentRegion
        Call CopyTranspose
        Set rgCriteria = .Range("A15").CurrentRegion
        Set rgOutput = .Range("F1")
    
        .Range("F1:L7").ClearContents
    End With
    rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

End Sub

Sub CopyTranspose()
    Range("A15:A20").EntireRow.ClearContents
    transposeAndPasteCol Range("A10:B14"), Range("A15")
End Sub

Sub transposeAndPasteCol(ColToCopy As Range, pasteRowTarget As Range)
    pasteRowTarget.Resize(ColToCopy.Columns.Count, ColToCopy.Rows.Count) _
     = Application.WorksheetFunction.Transpose(ColToCopy.Value)
End Sub

It worked! Thank you so much! Is there anyway that the customerID's in column F and G can be stacked vertically instead of horizontally? — Bikat Uprety, Feb 25 '22 at 17:14
I have tried but no! Updated my answer with the only option I see. — simple-solution, Feb 26 '22 at 16:02
While uploading update1 I thought of having a transposed version of rgCriteria. This does not work. But it simplifies the TransposeCopy. Therefor see update 2! ;-) — simple-solution, Feb 26 '22 at 16:15
I've used the CopyTranspose function proposed by @GollyJer at https://stackoverflow.com/questions/8852717/excel-vba-range-copy-transpose-paste — simple-solution, Feb 26 '22 at 16:53

Excel VBA Filter out table rows depending on cell value

1 Answers1