Comparing Filtered Columns

[wvanbusk111]

Hey all-been working at this for a couple days and could use some input.

What I'm attempting to accomplish with this code is to compare two sets of data on separate sheets(same workbook) and populate a third sheet with exceptions. I'm looking to have a list of what's missing in both of the two sheets compared(with respect to each other). The good news is that I've found/figured out how to do that fairly efficiently. So here's the challenge-I'm also filtering the values prior to running the compare vba and would only like to compare and display filtered values.

So...Goal:

1.)Compare and return exceptions of filtered values only

Here's my code:

Sub comparevalue()    
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long, rng1 As Range, rng2 As Range, c As Range 
    Set sh1 = Sheets("SHEET1") 
    Set sh2 = Sheets("SHEET2") 
    Set sh3 = Sheets("SHEET3") 
    lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row with data for both list sheets
    lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row 
    Set rng1 = sh1.Range("I2:I" & lr1) 'Establish the ranges on both sheets
    Set rng2 = sh2.Range("A2:A" & lr2) 
    With sh3 
        If .Range("A1") = "" And .Range("B1") = "" Then 
            .Range("A1") = "Extras in List 1" 
            .Range("B1") = "Extras in List 2" 
        End If 
    End With 
    For Each c In rng1 'Run a loop for each list ID mismatches and paste to sheet 3.
        If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then 
            sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value 
        End If 
    Next 
    Stop 
    For Each c In rng2 
        If Application.CountIf(rng1, c.Value) = 0 Then 
            sh3.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value 
        End If 
    Next 
End Sub

After some research, I've also figured out that I'll likely need to use specialcells(xlcelltypevisible) in some manner. As of right now, I can only get it to return either all the values, regardless of the filter, or 1 value, again regardless of the filter-depending on how I could specialcells into the vba. I didn't include it in my code above because I didn't want to "muddy" up what's working or present a rabbit trail I've been following as a solution....Thanks for any help in advance!

By the way-I'm using Excel 2010

 

[JLGWhiz]

If you want to compare unique values of the two columns in sheet 3, then you could use AdvancedFilter which has a feature to copy the unique values to another column. You could then run your compare macro against those two columns. He is the advanced filter macro for sheet 3, assuming that columns 4 and 5 are currently unused.

Sub filtstuff()
Dim sh3 As Worksheet, lr As Long
Set sh3 = Sheets("Sheet3")
lr = sh3.Cells.Find("*", sh3.Range("A2"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
sh3.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , sh3.Range("D1"), True
sh3.Range("B1:B" & lr).AdvancedFilter xlFilterCopy, , sh3.Range("E1"), True
End Sub

With all that said, I am wondering if you could not achieve the same end by puttin column B data under column A data and using advanced filter to get the unique values between the two sheets. or maybe you would rather keep them separate so you can tell which has what. Just thinking out loud here.