+ Reply to Thread
Results 1 to 3 of 3

Copy a range to cells above range

  1. #1
    petebud
    Guest

    Copy a range to cells above range

    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


  2. #2
    Executor
    Guest

    Re: Copy a range to cells above range

    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


  3. #3
    petebud
    Guest

    Re: Copy a range to cells above range

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1