Filter for text that does not match in two different columns

[Milos]

Hi all,

I have a Sub that currently works to automatically filter 3 different words in two columns in my workbook. What I am after is for an change up of this macro: so that it will filter when column 1 = "SentProgress" does not match column 2 = "ReceivedProgress". An example of this would when a specific row in column 1 has a "Yes" and the same matching row in column 2 has a "No" etc.

Does anyone have some good ideas on how I would alter my code for this? This is what my current code looks like:

Sub Filter_Dates_Matching()


Dim sent As Range
Dim received As Range


Set sent = Range("2:2").Find("SentProgress", , , xlWhole, , , False, , False)
Set received = Range("2:2").Find("ReceivedProgress", , , xlWhole, , , False, , False)


If Not sent Is Nothing Then
ActiveSheet.Rows(2).AutoFilter
ActiveSheet.Rows(2).AutoFilter Field:=sent.Column, Criteria1:=Array("Yes", "No", "NA"), Operator:=xlFilterValues
ActiveSheet.Rows(2).AutoFilter Field:=received.Column, Criteria1:=Array("Yes", "No", "NA"), Operator:=xlFilterValues
End If


End Sub

Any help on this would be appreciated!
Thanks,
Milos

 

[Fluff]

If you're happy using a helper column, try

Sub Filter_Dates_Matching()
   Dim sent As Range
   Dim received As Range
   
   Set sent = Range("2:2").Find("SentProgress", , , xlWhole, , , False, , False)
   Set received = Range("2:2").Find("ReceivedProgress", , , xlWhole, , , False, , False)
   
   If Not sent Is Nothing Then
      With Cells(2, Columns.Count).End(xlToLeft).Offset(1, 1)
         .Resize(Range("A" & Rows.Count).End(xlUp).Offset(-2).Row).FormulaR1C1 = "=(rc" & sent.Column & "<>rc" & received.Column & ")"
         .AutoFilter .Column, True
      End With
   End If
End Sub

 

[Milos]

Thanks Fluff! I am new the usage of a helper column but I think this will work great for my checks that i need to perform.

It worked for the first of my 10 worksheets in my workbook that I use this for but I am experiencing a run-time error ('1004' Autofilter method range class failed) with this section of the code that you made:

.AutoFilter .Column, True

How would I be able to use this without this error occurring?

Cheers,
Milos

 

[Fluff]

Could you supply your entire code?

 

[Milos]

Thanks Fluff I figured it out to make it work for my worksheets. The complete code is below. As I am still pretty nooby with my coding if there is any improvement that you can see let me know.

Sub Filter_Sent_Received_Not_Matching()


   Dim sent, received, blank As Range
            
   Set sent = Range("2:2").Find("SentProgress", , , xlWhole, , , False, , False)
   Set received = Range("2:2").Find("ReceivedProgress", , , xlWhole, , , False, , False)
   Set blank = Range("2:2").Find("", , , xlWhole, , , False, , False)
        
   If Not sent Is Nothing Then
        With Cells(2, Columns.Count).End(xlToLeft).Offset(1, 1)
                .Resize(Range("A" & Rows.Count).End(xlUp).Offset(-2).Row).FormulaR1C1 = "=(rc" & sent.Column & "=rc" & received.Column & ")"
        End With
                ActiveSheet.Rows(2).AutoFilter
                ActiveSheet.Rows(2).AutoFilter Field:=blank.Column, Criteria1:=Array("FALSE"), Operator:=xlFilterValues
   End If
End Sub

Cheers,
Milos

 

[Fluff]

Glad you got it working & thanks for the feedback.
Your code looks fine