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.

Solved: Excel VBA moving all data to columns A+B

Discussion in 'Business Applications' started by Gram123, Jun 18, 2014.

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

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I have data organised in paris of columns with differing amounts of data in each column pair.
    I need to move all data to columns A&B:

    - If there is data in columns C & D, move it to the foot of columns A&B.
    - Then, if there is data in columns E & F, move it to the foot of columns A&B.
    - Etc. til the end of the ActiveRange.

    I've managed to do this using hard-coded column references, but it involves a lot of reptetition.
    Presumably the code can be substantially compacted, and work to the end of the ActiveRange rather than to arbitrary end columns (in this case, cols AC and AD).
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi


    This will work on the active sheet.... If you need this for multiple sheets we would need to alter the macro slightly. Test this on a copy of your xl file.


    Code:
    Sub appAB()
    
    Dim i As Integer
    Dim lRow As Integer
    Dim lRowA As Integer
    
    For i = 3 To 30 Step 2
        lRow = Cells(Rows.Count, i).End(xlUp).Row
        lRowA = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Range(Cells(1, i), Cells(lRow, i + 1)).Cut Destination:=Range("A" & lRowA)
    Next
    
    End Sub
    
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Fantastic, cheers XCubed!

    I amended the FOR line to:
    To deal with potentially wider spans of data.

    (y)
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Nice addition. Alternatively, since, for some reason I don't completely trust UsedRange, you could use this;


    Code:
    For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    
     
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/1128060

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice