CODEWITHSUNDEEP
excellist
user2443084
user2443084

Reputation:

vba comparing similar lists

I have 2 sheets with very similar lists. Example

Column A   Column B
1          1
2          3
3          3.5
4          4
4.5        5
5

I should add the missing elements to B and then delete the extra elements from B.I'm not looking for copypaste, this numbers are actually links and I'll run macros before copying and deleting. so

Call add(2)
Call add(4.5)
Call remove(3.5)

Is there a faster way than going 

for (all elements in A)
   for (all elements in B)
      if (they match) then mark as ok
delete all non marked in B
add all non marked in A

It feels slow, and I don't know how to 'mark'

Upvotes: 0

Views: 33

Answers (1)

Michael H
Michael H

Reputation: 11

Option Explicit

Sub test()

    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long

    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    Debug.Print Now

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                ' Cells are identical.
                ' Do nothing.
            Else
                ' Cells are different.
                ' Code goes here for whatever it is you want to do.
            End If
        Next iCol
    Next iRow

End Sub

Try using this setup and within the else statement you need to create a print string so it can say if this value matches this

Upvotes: 1

Related Questions