1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help! Excel Macro: How to move every 2nd row to next column?

Discussion in 'Business Applications' started by misuszatek, Aug 3, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. misuszatek

    misuszatek Thread Starter

    Joined:
    Aug 3, 2009
    Messages:
    2
    I barely use Excel but I have a list to modify / reformat.

    What I have is about 25,000 names and addresses in one column:

    address 1
    _________
    name1
    _________
    address 2
    _________
    name2
    _________
    address 3
    _________
    name 3


    what I want to achieve is 2 columns where one is address , other is names

    __________________
    address 1 | name1
    __________________
    address 2 | name 2
    __________________
    address 3 | name 3
    __________________


    etc .

    Can you help me out with macro to move every 2nd cell / row in first column and move them to next column , like it's shown in example above?

    thank you for any help ;)
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Hi
    Try this code:

    Code:
    Sub Test()
        Dim EvenCell As Range
        
        [COLOR="Red"]Set EvenCell = Range("A2")[/COLOR]
        Do While EvenCell <> ""
            With EvenCell
                .Copy .Offset(-1, 1)
                .ClearContents
            End With
            Set EvenCell = EvenCell.Offset(2)
        Loop
        Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    It is assumed that the first Name field (name1) is in cell A2. If not, change the red code line accordingly.
    Note: the macro uses the active worksheet, so make sure you activate the sheet with the data before running it.

    Jimmy
     
  3. misuszatek

    misuszatek Thread Starter

    Joined:
    Aug 3, 2009
    Messages:
    2
    thank you, it works..almost :D

    ...the only thing is ..once cells are moved ...whole sheet is cleared. Should I change some settings or define the range or something?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    No need for a macro.

    Assuming address1 is in A1, use:

    =INDIRECT("A"&(ROW()*2-1))

    in (e.g.) C1, and:

    =INDIRECT("A"&ROW()*2)

    in D1. Copy these down as far as you need.

    Then with cols C & D selected, use Copy > Edit > Paste Special > Values to "hard-code" the formula results.
     
  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Well, maybe column A was empty. In that case the code really deletes everything.
    I think with this little tweak it should work (of course, change the A2 to what is required).
    Code:
    Sub Test()
        Dim EvenCell As Range
        
        Set EvenCell = Range("A2")
        Do While EvenCell <> ""
            With EvenCell
                .Copy .Offset(-1, 1)
                .ClearContents
            End With
            Set EvenCell = EvenCell.Offset(2)
        Loop
        EvenCell.EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/848970