comparing columns of numbers

  • Thread starter Thread starter Natdan
  • Start date Start date
N

Natdan

Hi

If you have 2 columns of numbers and you want to find the numbers that are
in one columns but not the other, and you type say A10=B10 then when you find
a figure that is in one column but not the other you insert/delete a line and
then drag the formula down again to keep checking. Is there any better
formula I can use or anyway I can do it so I don't have to keep dragging down
the formula
 
Public Sub ProcessData()
Const TestCol1 As String = "A"
Const TestCol2 As String = "B"
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TestCol2).End(xlUp).Row
.Cells(LastRow + 1, TestCol2).Value = 9999999999999#
LastRow = .Cells(.Rows.Count, TestCol1).End(xlUp).Row
.Cells(LastRow + 1, TestCol1).Value = 9999999999999#
i = 2
Do

If .Cells(i, TestCol1).Value <> "" And .Cells(i, TestCol2).Value
<> "" Then

If .Cells(i, TestCol1).Value > .Cells(i, TestCol2).Value
Then

.Cells(i, TestCol1).Insert shift:=xlDown
i = i - 1
ElseIf .Cells(i, TestCol1).Value < .Cells(i, TestCol2).Value
Then

.Cells(i, TestCol2).Insert shift:=xlDown
i = i - 1
End If
End If
i = i + 1
Loop Until .Cells(i, TestCol1).Value = 9999999999999# And .Cells(i,
TestCol2).Value = 9999999999999#
LastRow = .Cells(.Rows.Count, TestCol1).End(xlUp).Row
.Rows(LastRow).Delete

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top