Copy a range to cells above range

[petebud]

Hello All,

This is my story.
I have 2 lists.
List 1, has UPDATED address info, List 2 has "out of date" addresses,
but current phone numbers.
I have combined the 2 lists and sorted them by Last Name, First Name.

Row 1 has John, Smith, (old)PO BOX, (old)City, (old)State, (old)zip,
phone number, cell phone
Row 2 has John Smith, (new)PO BOX, (new)City, (new)State, (new)Zip,
(blank)phone number, (blank)Cellphone

Every odd row has old address info and current phone info
Every even row has current addrerss info but no phone info

Not every odd row has phone info.
I also need to check for incorrect info, so i need a macro that i can
us on just 2 rows at a time.
This is what i have been doing: Select columns( of Row 2), copy, the
select same columns in Row 1, then Paste Special, Skip blanks.

This is working pretty good, but i have 20,000 rows to go through.
Is there a marco that could make this faster.
Bear in mind i need to be abel to nly check 2 rows at a time.

Thanks
Pete

 

[Executor]

Hi Pete,

Try this:

Sub CopyEvenToOdd()
'
Range("A2").Select
Do
Range(ActiveCell, ActiveCell.Offset(0, 8)).Select
Selection.Copy
ActiveCell.Offset(-1, 0).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
ActiveCell.Offset(3, 0).Select
Loop Until IsEmpty(ActiveCell)
Range("A1").Select
Application.CutCopyMode = False
End Sub

Hoop This Helps,


Executor

 

[petebud]

This should work just fine.
This shold give me enough code to work with. I dont need it to loop,
since there will be a few cases where the odd row and the even row are
not the same records. Of i could go into debug mode and step thorugh it
and just stop it when i come to a set of bad records.
Either way THANKS

Your QUICK help is very much APPRECIATED.

Thanks
Pete