Comparing two filtered columns?

Question

I have a spreadsheet that is filtered and I need to compare values of two columns, if they are all same in visible filtrated cells then macro a executes, and if any of them is different then macro b executes.

I already tried to define ranges as:

Set rng1 = Range("X:X").Cells.SpecialCells(xlCellTypeVisible)

Set rng2 = Range("AU:AU").Cells.SpecialCells(xlCellTypeVisible)

and define values of the first visible range

valE = ActiveSheet.Range("X:X").Cells.SpecialCells(xlCellTypeVisible).Value

valX = ActiveSheet.Range("AU:AU").Cells.SpecialCells(xlCellTypeVisible).Value

I don't know how to write a loop that iterates through specified range comparing next visible rows. What should I refer to?

First, I would restrict the `Range` to something smaller than the entire column... — BigBen, Sep 17 '19 at 18:15
yes, but macro purpose is for multi-sheet so everytime there can be different amount of records, which i can't predefine. I'm quite new to vba, sorry if i ask stupid questions :P — Michał Żuromski, Sep 17 '19 at 18:18
Then maybe you can [find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Sep 17 '19 at 18:19
FWIW parameterless `Range.Cells` is fully redundant 99% of the time. — Mathieu Guindon, Sep 17 '19 at 18:25
Also - a non-contiguous range (assuming when you filter that non-contiguous rows are visible) - can't be read into an array like that, if I understand what you're trying to do. — BigBen, Sep 17 '19 at 18:30
Will the last row always be the same for both columns (X and AU)? — BigBen, Sep 17 '19 at 19:43
And if you're thinking what I'm thinking, you may be able to use `Offset`, yes. — BigBen, Sep 17 '19 at 19:58

score 0 · Answer 1 · answered Sep 17 '19 at 20:16

Sub REName_()

    Dim r1 As Range, _
            r2 As Range

' your code
Set rng1 = Range("X:X").Cells.SpecialCells(xlCellTypeVisible)
Set rng2 = Range("AU:AU").Cells.SpecialCells(xlCellTypeVisible)
'

    If Ranges_Filtered_Compare_Visible(rng1 , rng2 ) Then
        'a
    Else
        'b
    End If

End Sub

Function Ranges_Filtered_Compare_Visible( _
        r1 As Range, _
        r2 As Range) _
        As Boolean

    Dim wb As Workbook, _
            ws As Worksheet

    Set wb = Workbooks.Add
    Set ws = wb.ActiveSheet

    With ws
        r1.Copy .Cells(1, 1)
        r2.Copy .Cells(1, 2)

        If Columns_next_door_compare(.Cells(1, 1)) Then

            Ranges_Filtered_Compare_Visible = True

        End If
    End With

    wb.Close False

End Function

Function Columns_next_door_compare( _
        ceLL As Range) _
        As Boolean

    Dim r As Range
    Set r = ceLL.CurrentRegion.Columns(1)

    Dim bCells_Equal As Boolean
    bCells_Equal = True

    For Each ceLL In r.Cells

    With ceLL
        If .Value <> .Offset(0, 1).Value Then

            bCells_Equal = False

            Exit For

        End If
    End With

    Next

    Columns_next_door_compare = bCells_Equal

End Function

Wow that actually solved the issue completely, thanks! I have a question tho, is there a way to highlight cells that are not matching in original sheet after this code executes? — Michał Żuromski, Sep 18 '19 at 18:48

score 0 · Answer 2 · answered Sep 19 '19 at 05:52

Sub Range_Compare_Color( _
    r1 As Range, _
    r2 As Range, _
    lColor As Long)
    ' slowly and solemnly
    ' paint in the first range of a cell that is not in the second

    Dim ceLL As Range

    For Each ceLL In r1

        With ceLL

            If inRange(.Value, r2) = False Then

                .Interior.Color = lColor

            End If
        End With
    Next
End Sub

Function inRange( _
         s As String, _
         r As Range) _
         As Boolean

    Dim found As Range
    Set found = r.Find(s)

    If Not found Is Nothing Then

        inRange = True

    End If
End Function

Comparing two filtered columns?

2 Answers2