asked on

VBA: filter multiple data from different sheets based on a dynamic range

Hello experts,

I use the following vba in order to filter column A of every sheet except (Runner2) based on the information report in B1.

Sub Macro1()

    ActiveSheet.Range("$A$1:$E$366").AutoFilter Field:=1
    ActiveSheet.Range("$A$1:$E$366").AutoFilter Field:=1, Criteria1:="=*1111*" _
        , Operator:=xlAnd
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WS As Worksheet
    If Target.Row = 1 And Target.Column = 2 Then
        v = Cells(Target.Row, Target.Column)
        For Each WS In Worksheets
            If LCase(WS.Name) <> "runner" Then
                WS.Range("$A:$E").AutoFilter Field:=1
                WS.Range("$A:$E").AutoFilter Field:=1, Criteria1:="=*" & v & "*", Operator:=xlAnd
            End If
        Next
    End If
End Sub

Open in new window

I would like to add an additional requirement to the vba:

The filter need to take into account not just the information of Range B1 but all the information report in column B ex:

Thank you again for your help.
Check-logs-V2.xlsm

Luis Diaz

ASKER

For your info, I use the following vba to filter based on a range:

 Sub auto_filter_based_on_range()

'-----------------------------------------------------------'
'If you want to setup a permanent Activecell in columns J
'lastrowcolJ = Range("J65533").End(xlUp).Row
'Range("J2:J" & lastrowcolJ)
'-------------------------------------------------------------'

 Dim Arr As Variant
 Dim cn As Integer
    Dim i As Integer
    On Error GoTo exit_sub
 cn = ActiveCell.Column
 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 
 Arr = Application.InputBox(prompt:="Enter the range to filter", Type:=8)
 
 Arr2 = Application.Transpose(Arr)
 
 For i = LBound(Arr2) To UBound(Arr2)
        Arr2(i) = CStr(Arr2(i))
    Next i
 
ActiveSheet.UsedRange.AutoFilter Field:=cn, Criteria1:=Arr2, Operator:=xlFilterValues
exit_sub:
End Sub

Open in new window

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Ok, it works, but is not a way to apply another method rather than AutoFilter?

Thank you again for your help.

Ryan Chong

>>but is not a way to apply another method rather than AutoFilter?
as far as I know, if we are using AutoFilter, maximum only 2 criteria can be used.

Personally thinking if you want to have more than 2 criteria, then we may need to use dynamic query to get the results you wanted, like using a .dqy file which generated from Microsoft Query that embedded in Excel.

Luis Diaz

ASKER

Oki,

But I know that we can filter more than two criterias with an array something like this:

Sub auto_filter_based_on_range()

'-----------------------------------------------------------'
'If you want to setup a permanent Activecell in columns J
'lastrowcolJ = Range("J65533").End(xlUp).Row
'Range("J2:J" & lastrowcolJ)
'-------------------------------------------------------------'

 Dim Arr As Variant
 Dim cn As Integer
    Dim i As Integer
    On Error GoTo exit_sub
 cn = ActiveCell.Column
 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 
 Arr = Application.InputBox(prompt:="Enter the range to filter", Type:=8)
 
 [b]Arr2 = Application.Transpose(Arr)[/b]
 
 For i = LBound(Arr2) To UBound(Arr2)
        Arr2(i) = CStr(Arr2(i))
    Next i
 
ActiveSheet.UsedRange.AutoFilter Field:=cn, Criteria1:=Arr2, Operator:=xlFilterValues
exit_sub:
End Sub

Open in new window

Is not a way to take combine this range filter based on the array (instead of having application.inputbox we define the range in that case Range("B2:B" & Lastrow)?

Thank you again for your help.

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much for this proposal.

I tested your code but I don't have the expected result:

When your private sub is called based on the following information:

I got the following result:

And I should have the following result:

Thank you again for your help.

Subodh Tiwari (Neeraj)

The code is for Worksheet Change Event and should be place on Runner2 Sheet Module.
And the code will be triggered once you change the values in col. B.

I have placed the code in the sheet module and it is returning the output what you are expecting it to return.

For details see the attached.
Check-logs-V2-1.xlsm

Luis Diaz

ASKER

Perfect, it works, however I see that the filter is not applied for the second sheet CCWT_requests.

Thank you again for your help.

Subodh Tiwari (Neeraj)

The code doesn't apply filter on the sheets rather the code hides and unhides the rows as per the criteria and it takes place every time you change the values in col. B on Runner 2 Sheet.

Luis Diaz

ASKER

Thank you for your comment.
In that case is there a way to hide and unhide the rows of all the sheets <> runner2. The thing is that the code should be applied for the various sheets <>runner and not just for the first sheet CCWT_requests.

Thank you again for your help.

Subodh Tiwari (Neeraj)

Since you have this condition to check If LCase(ws.Name) <> "runner2" Then, the code will be executed on all the sheets in the workbook except runner2.

Isn't it doing the same thing?

The sample workbook I uploaded with my last reply had three sheets and code successfully executed on rest of two sheets not only on first sheet other than runner2. Didn't it?

aikimark

You should be using an advancedfilter not a regular filter.

aikimark

I added a routine (Q_28707160) in the (new) Module3. It applies an advancedfilter to the non-Runner2 worksheets.

Note: you can hide the criteria column (C) on the Runner2 worksheet. I protected the column with a password=28707160
Check-logs-V2.xlsm

Luis Diaz

ASKER

Perfect I tested and it works, however is there a way to have the data refresh automatically.
With the current version I need to enter the data in column B and run the macro to hide the values. Is there a way to enter the data in column B and automatically hide the values without launching the macro.

Thank you very much for your help.

Luis Diaz

ASKER

I found also something strange.

When I want to filter one value with your code I have the following result:

In order to get the expected result I need to enter as following the information

Thank you in advance for your help.

aikimark

Ah. I didn't test the single item filter condition. Hang on.

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

@aikimark,

the scripts you posted in comment: ID: 40939780 seems doesn't work well IF the last value entered in Runner2's Column B can not be found in respective worksheets.

it seems that it only checks for the last value.

Subodh Tiwari (Neeraj)

@Ryan

I think both of the codes i.e. mine and aikimark's work okay if the criteria value in column B is not found in the target sheets.

Did you try to run the code present on Module3 of the workbook aikimark uploaded?

Though still I am not sure what issue OP is having with my code as it produces the desired output.

Ryan Chong

@sktneer

Ok, now tested both yours and aikimark's solutions, both working nicely. There was an extra "IF" statement in my own codes which stop the scripts to be executed. My bad... but thks for highlighting.

Subodh Tiwari (Neeraj)

No problem Ryan!
Thanks for confirming that my code also worked as per the OP's requirement. :)

aikimark

You have an empty cell in your column B values. The example data you posted only showed contiguous data values. VBA code can removed empty cells from column B, but I did not include that because it wasn't in your posted examples.

Does the filtering work if you remove the empty cell?

Luis Diaz

ASKER

Thank you all for your solutions.