Excel VBA advanced filter to exclude data

0 votes

I'm using customer reference numbers to filter data in a table. Can I filter off rows depending on their customer reference number using a VBA code?

I'm employing a very useful code I saw online that contains information based on a client reference number and looks like this:

Dim rgData As Range, rgCriteria As Range, rgOutput As Range

Set rgData = ThisWorkbook.Worksheets("Sheet 1").Range("A1").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Sheet 1").Range("I1").CurrentRegion
Set rgOutput = ThisWorkbook.Worksheets("Sheet 2").Range("A1")

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

The code first includes just the customers discovered in the table on sheet 1 that begins in cell "I1," then it moves on to sheet 2 and only includes customers in the table that begins in cell "A1" who have customer reference numbers.

Is there a way that I could change the above code so that it excludes those customer reference numbers found in the table in cell "I1"?

Nov 29, 2022 in Others by Kithuzzz
• 38,010 points
804 views

1 answer to this question.

0 votes

Try this:

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("D1").CurrentRegion
        Set rgOutput = .Range("F1")
    
        .Range("F1:G7").ClearContents
        rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

        Set rgData = .Range("A8").CurrentRegion
        Set rgCriteria = .Range("D8").CurrentRegion
        Set rgOutput = .Range("F8")
    
        .Range("F8:G15").ClearContents
        rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
    End With
End Sub

enter image description here

answered Dec 10, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Searching Multiple Criteria In Large Data Set to make new Data Set Excel VBA

Copy the data to an array, filter ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,420 points
649 views
0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,420 points
566 views
0 votes
1 answer

Excel VBA function that cross references two data sets to come up with one solution

Solution using dictionary to count Public Sub citizens() ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
428 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
836 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,138 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
433 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
698 views
0 votes
1 answer

How to use data validation and 'no filter' in excel

eventually succeeded in resolving this. Effectively, Filter() ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
299 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
1,477 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP