Check Values/Insert Rows

[Dan R.]

Say I have a spreadsheet that has these values on Sheet1 Column A:
A
B
C
D
E

Then I copy & paste this onto Sheet2 Column A:
A
C
E

I need a macro to automatically recognize the missing values and
insert rows accordingly... I know this isn't hard but I can't think of
how to do it.

Thanks,
-- Dan

 

[matt]

Say I have a spreadsheet that has these values on Sheet1 Column A:
A
B
C
D
E

Then I copy & paste this onto Sheet2 Column A:
A
C
E

I need a macro to automatically recognize the missing values and
insert rows accordingly... I know this isn't hard but I can't think of
how to do it.

Thanks,
-- Dan

If the letters are always a-z then you could use the Chr function
(65-90 is A - Z; 97-122 is a - z) in a For...Next loop. (e.g.
Chr(119) = w). You can load the 2 sets into arrays and then compare
the arrays.

Matt

 

[Dan R.]

Unfortunately the letters won't always be A-Z, I just used that as an
example. This is what I've come up with so far... it's pretty close.
Am I missing something?

Sub Test()
Dim i As Range
Dim iRng As Range
Dim x As Integer

Set ws = ActiveSheet
Set Q = Sheets(1)

Set iRng = ws.Range(Cells(1, 1), Cells(6, 1))

With ws
For Each i In iRng
For x = 1 To 5
If i.Value = Q.Cells(x, 1).Value Then
i.EntireRow.Cut .Cells(x, 1)
End If
Next
Next
End With

End Sub

Thanks,
-- Dan