Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

misuszatek's Avatar
misuszatek misuszatek is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Aug 2009
03-Aug-2009, 09:48 PM #1
Question Help! Excel Macro: How to move every 2nd cell to next column?
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

Last edited by misuszatek; 03-Aug-2009 at 10:45 PM..
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
04-Aug-2009, 07:58 AM #2
Hi
Try this code:

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
    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
misuszatek's Avatar
misuszatek misuszatek is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Aug 2009
04-Aug-2009, 11:25 AM #3
thank you, it works..almost

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

Last edited by misuszatek; 04-Aug-2009 at 11:42 AM..
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Aug-2009, 03:23 PM #4
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.
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
04-Aug-2009, 06:16 PM #5
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑