CODEWITHSUNDEEP
excelvba
MelBee
MelBee

Reputation: 67

Getting 2 non-adjacent columns to work with

I am trying to build a macro that compares two columns to see whether or not they are the same. This is the function.

Sub Compare()
Dim LastRow As Integer
LastRow = Worksheets("Sheet1").Range("A1048576").End(xlUp).Row 
For i=2 to LastRow
    If Range("A" & i).Value = Range("B" & i).Value Then
        Range("C" & i).Value = True
    Else
        Range("C" & i).Value = False
    End If
End Sub

Right now it is hard coded to compare column A and B then output True/False into column C. What I want to do is have the user select ONLY 2 columns from their excel sheet and then run this macro which then compares the two selected columns by the user. These 2 columns can be non-adjacent to each other, so for example the user can select column A and column K to compare the values between them.

Upvotes: 1

Views: 101

Answers (1)

user4039065
user4039065

Reputation:

You need to check the .Areas property of non adjacent columns.

Sub CompareTwo()
    dim rng as range, i as long
    set rng = intersect(selection, selection.parent.usedrange)

    if rng.areas.count>1 then
        for i=1 to rng.areas(1).rows.count
            rng.areas(2).cells(i).offset(0, 1) = cbool(rng.areas(1).cells(i).value = rng.areas(2).cells(i).value)
        next i
    else
        for i=1 to rng.rows.count
            rng.cells(i).offset(0, 2) = cbool(rng.cells(i).value = rng.cells(i).value)
        next i
    end if
End Sub

To put the results in a new column at the end of the excel sheet, use Find with xlPrevious and xlByColumns to locate the last used column then offset 1.

Upvotes: 2

Related Questions