Add another condition to Filter code

[Nick70]

Hi,

I have a code for a filter see below:

Sub ApplyFilter()
 
    Dim filterRange As Range
    Dim filterConditions As Range

    'Set the filter range and conditions range
    Set filterRange = Sheets("Sheet2").Range("A4:G20")
    Set filterConditions = Sheets("Sheet1").Range("A5:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)

    'Apply the filter based on the conditions in Sheet1
    filterRange.AutoFilter Field:=4, Criteria1:=Application.Transpose(filterConditions), Operator:=xlFilterValues

End Sub

I would like to change filterConditions so that is also looks at data in column B.
So code would change to something like

    Set filterConditions = Sheets("Sheet1").Range("A5:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row) &  Sheets("Sheet1").Range("B5:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row)[/B]

But this does not work.

Can someone please let me know how I can add additional condition

Set filterConditions = Sheets("Sheet1").Range("B5:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row)

?

Thanks,
N.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Nick70]

Hello I use Excel version 2208 I will update my Account details

 

[Nick70]

I use office version 365 I have added this to my Account details. I hope someone will still answer my question as it shows it has some replies to it although they are not the answer.
Thanks,
N.

 

[Fluff]

I hope someone will still answer my question

Give me a chance.

How about

Sub ApplyFilter()
 
    Dim filterRange As Range
    Dim filterConditions As Variant

    'Set the filter range and conditions range
    Set filterRange = Sheets("Sheet2").Range("A4:G20")
    filterConditions = Evaluate("tocol(" & Sheets("Sheet1").Range("A5:B" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row).Address & ",1)")

    'Apply the filter based on the conditions in Sheet1
    filterRange.AutoFilter Field:=4, Criteria1:=Application.Transpose(filterConditions), Operator:=xlFilterValues

End Sub

 

[Fluff]

A better version

Sub ApplyFilter()
 
    Dim filterRange As Range
    Dim filterConditions As Variant

    'Set the filter range and conditions range
    Set filterRange = Sheets("Sheet2").Range("A4:G20")
    With Sheets("Sheet1")
        filterConditions = .Evaluate("torow(" & .Range("A5:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Address & ",1)")
    End With
    'Apply the filter based on the conditions in Sheet1
    filterRange.AutoFilter Field:=4, Criteria1:=filterConditions, Operator:=xlFilterValues

End Sub

 

[Nick70]

It works perfectly!

Thanks a apologies for doubting you would give answer!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Nick70]

A better version

Sub ApplyFilter()
 
    Dim filterRange As Range
    Dim filterConditions As Variant

    'Set the filter range and conditions range
    Set filterRange = Sheets("Sheet2").Range("A4:G20")
    With Sheets("Sheet1")
        filterConditions = .Evaluate("torow(" & .Range("A5:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Address & ",1)")
    End With
    'Apply the filter based on the conditions in Sheet1
    filterRange.AutoFilter Field:=4, Criteria1:=filterConditions, Operator:=xlFilterValues

End Sub

Hello Fluff,

As if life was not difficult enough...

In reality my two ranges in the two columns are different (column B has more data) so we should take them separately and add them together.
Current code takes last row from column A but in reality column B has more data so we will miss some data from column B.

Do you know how to amend code?

Thanks,
N.

 

[Fluff]

How about

Sub ApplyFilter()
 
    Dim filterRange As Range
    Dim filterConditions As Variant
    Dim Lr As Long
    
    'Set the filter range and conditions range
    Set filterRange = Sheets("Sheet2").Range("A4:G20")
    With Sheets("Sheet1")
        Lr = .Range("A:B").Find("*", , , , , xlPrevious, , , False).Row
        filterConditions = .Evaluate("torow(" & .Range("A5:B" & Lr).Address & ",1)")
    End With
    'Apply the filter based on the conditions in Sheet1
    filterRange.AutoFilter Field:=4, Criteria1:=filterConditions, Operator:=xlFilterValues

End Sub