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
– Bikat Uprety, Commented Feb 25, 2022 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
– BigBen, Commented Feb 25, 2022 at 15:06

simple-solution · Accepted Answer · 2022-02-26 16:55:50Z

0

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

edited Feb 26, 2022 at 16:55

answered Feb 25, 2022 at 15:35

simple-solution

1,1491 gold badge6 silver badges13 bronze badges

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

4 Comments

Bikat Uprety Over a year ago

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?

simple-solution Over a year ago

I have tried but no! Updated my answer with the only option I see.

simple-solution Over a year ago

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 Over a year ago

I've used the CopyTranspose function proposed by @GollyJer at stackoverflow.com/questions/8852717/…

Collectives™ on Stack Overflow

Excel VBA Filter out table rows depending on cell value

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related