0

I am trying to make an excel code that will compare the used range of Rows 1 and 2 of the same worksheet and delete any similar cells and move the remaining (unique values) cells to Row 1 beginning at A1.

eg) If row 1 contains these values (commas inidicate diff cells): a, b, c and row 2 contains: a, b, c, d, e

I want the code to compare the two rows and end up with row 1 being: d, e (in columns A and B), after the code is complete. Any help would be appreciated.

Im new to VBA so im having trouble on some syntax that I would appreciate if some pros could help me out.

  1. Get the used number of columns for rows 1 and 2 as integers. eg) maxCol1 = 3, maxCol2 = 5

  2. Create a for loop that goes from i = 1 To maxCol2 and compares row 1 to row 2. if they are equal, make them both "", if there is something in row 2 but not in row 1, set that value to cell A1.

basically just need help on setting step 1 up.

Community
  • 1
  • 1
A.Cod
  • 71
  • 2
  • 9
  • try userdange.resize or usedrange.rows(1) etc. Load both the used ranges in arrays , loop and compare, keep final values in an array and in the end dump the array on the worksheet. For all these steps you can find code on SO. – cyboashu Jul 11 '16 at 19:14
  • sweet, thanks so much – A.Cod Jul 11 '16 at 19:19
  • This one might be helpful: http://stackoverflow.com/a/33601498/293078 – Doug Glancy Jul 11 '16 at 20:33
  • Thanks! that actually does help. just one question however: do you know if a collection keeps its contents even after the loop? for example if i wanted the values were saved into the collection, would the collection need to be emptied and values need to be put in again for a different run through? – A.Cod Jul 12 '16 at 15:27

1 Answers1

0

With the help of the link posted in the comment, I figured it out! Thanks to those who helped. The code compares row 2 from row 1 and deletes any similar cell values and posts the unique values into row 1 and also into a new worksheet.

 Sub CompareAndDelete()
'This code will compare the rows of each sheet and delete any old alerts that have already been emailed out
'   it will then call SaveFile IF new alerts have been found

Dim row1() As Variant, row2() As Variant, newRow As Variant
Dim coll As Collection
Dim i As Long
Dim maxCol1 As Integer
Dim maxCol2 As Integer

'Find max number of columns for old and new alert
With ActiveSheet
    maxCol1 = .Cells(1, .Columns.Count).End(xlToLeft).Column
    maxCol2 = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With

'Redimensionalize arrays
ReDim row1(0 To (maxCol1 - 1))
ReDim row2(0 To (maxCol2 - 1))

'Assign row1/row2 string values into arrays
For r = 0 To (maxCol1 - 1)
   row1(r) = Cells(1, r + 1).Value
Next

For s = 0 To (maxCol2 - 1)
    row2(s) = Cells(2, s + 1).Value
Next

ReDim newRow(LBound(row1) To Abs(UBound(row2) - UBound(row1)) - 1)

'Create a collection to load all row1/row2 values into
Set coll = New Collection

'Empty Collection for each run through
Set coll = Nothing

'Set collection to New before using
Set coll = New Collection



For i = LBound(row1) To (UBound(row1))
    coll.Add row1(i), row1(i)
Next i

For i = LBound(row2) To (UBound(row2))
    On Error Resume Next
    coll.Add row2(i), row2(i)
    If Err.Number <> 0 Then
        coll.Remove row2(i)
    End If
    On Error GoTo 0
Next i

'Copy Row 2 and Paste it to Row 1

ActiveWorkbook.ActiveSheet.Rows(2).Copy
Range("A1").Select
ActiveSheet.Paste

'Now values are stored in collection, delete row 2
'Rows(2).EntireRow.ClearContents

'Paste only the new alerts onto a new worksheet that is designated for new   alerts
For i = LBound(newRow) To UBound(newRow)
    newRow(i) = coll(i + 1) 'Collections are 1-based
    'Debug.Print newRow(i)
    ActiveWorkbook.Sheets("Sheet" & index + 4).Select
    ActiveWorkbook.Sheets("Sheet" & index + 4).Cells(1, i + 1).Value =   newRow(i)

Next i


'if NEW alerts have been found, call SaveFile
If IsEmpty(ActiveWorkbook.Sheets("Sheet" & index + 4).Cells(1, 1)) = False     Then
         Call SaveFile
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A.Cod
  • 71
  • 2
  • 9