VBA - advanced filter help...

[Malleshg24]

Hi Team,

I am using below code and its giving Correct result.

Situation1
Column A, B, C, are my Raw data,
Situation1, Column F,G,H Are my filter Criteria List.{Filter In}

Situation2, Same situation but exclude Column J Criteria list (Filter Out)
Situation2 - Column J,K,L Are my filter Criteria List.


How to achieve same result via advanced filter in both Situation.
Criteria list my Increase.

if possible can you Suggest a dynamic function to cover Multiple Situations Like this. (10-15 Situation)

Option Explicit
Sub Count_as_Per_Criteria()

Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant

ar1 = Sheet1.Range("F3", Range("F" & Rows.Count).End(xlUp))
ar2 = Sheet1.Range("G3", Range("G" & Rows.Count).End(xlUp))
ar3 = Sheet1.Range("H3", Range("H" & Rows.Count).End(xlUp))

 Dim i As Long, Situation1 As Long, lr As Long
 lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
 
 'Situaction 1 Include all Criteria  ' Code is working
 For i = 2 To lr
    If IsNumeric(Application.Match(Sheet1.Cells(i, 1).Value, ar1, 0)) Then
         If IsNumeric(Application.Match(Sheet1.Cells(i, 2).Value, ar2, 0)) Then
            If IsNumeric(Application.Match(Sheet1.Cells(i, 3).Value, ar3, 0)) Then
                    Sheet1.Cells(i, 1).Resize(1, 3).Font.Color = vbBlue
                Situation1 = Situation1 + 1
            End If
        End If
    End If
Next i

MsgBox Situation1


'----------Situation2 Count--------------------

ar1 = Sheet1.Range("J3", Range("J" & Rows.Count).End(xlUp))
ar2 = Sheet1.Range("K3", Range("K" & Rows.Count).End(xlUp))
ar3 = Sheet1.Range("L3", Range("L" & Rows.Count).End(xlUp))

Dim Situation2 As Long

For i = 2 To lr
    If IsNumeric(Application.Match(Sheet1.Cells(i, 1).Value, ar1, 0)) = False Then 'Something wrong here, How to exclude.
         If IsNumeric(Application.Match(Sheet1.Cells(i, 2).Value, ar2, 0)) Then
            If IsNumeric(Application.Match(Sheet1.Cells(i, 3).Value, ar3, 0)) Then
                    Sheet1.Cells(i, 1).Resize(1, 3).Font.Color = vbRed
                Situation2 = Situation2 + 1
            End If
        End If
    End If
Next i

MsgBox Situation2


End Sub

Thanks
mg

 

[Marc L]

Hi,​

​

this should be helpful :​

Extract data using Advanced Filter and VBA » Chandoo.org - Learn Excel, Power BI & Charting Online

In this post we will learn how to use the Advanced Filter option using VBA to allow us to filter our data on a separate sheet. This has been requested by a lot of our readers and here is how we will use them.

chandoo.org

 

[Malleshg24]

Hi Mark L,

Thanks for your help, to some extent I know advanced filter how it works,
But Unable to fix it in my situation. Can you suggest how to apply in my situation ,

Most of the time I come across this situation.


Thanks
mg

 

[Marc L]

Hi Marc L,

As your code has nothing to do with the advanced filter method how can we guess you know it ?!​

​

Among many samples from this forum :​

• Filtering out required data
• Loop on All files of folder ,But Process Only specifically Listed (Partial Named)Files
• VBA to copy row automatically to another sheet based on cell value
• Extraction problem
• VBA to extract/copy/paste specific texts/words among a sentence in a cell
• Automatically Hide Line Based on Value of Cell
• Check whether a column has values within a value set
• Problem in Copying data from one workbook to another using VBA
• How to create separate Sheets workbook with name

 

[vletm]

Malleshg24
You've written ...
I am using below code and its giving Correct result.
How to achieve same result via advanced filter in both Situation.
Q: How someone else than You would know Your 'correct results' and compare those to something else?