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 Macro to Move Data in rows to columns

Discussion in 'Business Applications' started by maliaga, Oct 6, 2011.

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

    maliaga Thread Starter

    Joined:
    Oct 6, 2011
    Messages:
    4
    I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

    I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

    Thanks in advance for the help.
     

    Attached Files:

  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    See if this code helps.

    Code:
    Sub MoveData_ToColumn()
    Application.ScreenUpdating = False
    Dim lrow As Long
    Dim i As Long
    lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    For i = lrow To 2 Step -3
        Range("A" & i & ":C" & i).Copy Destination:=Range("B" & i - 1)
        Range("A" & i & ":C" & i).ClearContents
    Next
    End Sub
    
     
  3. maliaga

    maliaga Thread Starter

    Joined:
    Oct 6, 2011
    Messages:
    4
    This works great at moving the data around to the appropriate rows. Is there a way to compact the data table by removing the empty rows created by moving the data into the appropriate rows?

    Naturally, I could do this last step manually...but it would be great to get this all accomplished with one macro.

    Thanks again!
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi welcome to the forum,
    your code is fine, all you neeed is repeat the loop and delete the empty roes, I have attached your own code with the addition in red

    Code:
    Option Explicit
    
    Sub MoveData_ToColumn()
    Application.ScreenUpdating = False
    Dim lrow As Long
    Dim i As Long
    lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    For i = lrow To 2 Step -3
        Range("A" & i & ":C" & i).Copy Destination:=Range("B" & i - 1)
        Range("A" & i & ":C" & i).ClearContents
    Next
    
    [COLOR="Red"]For i = lrow To 2 Step -1
        If Cells(i, 1) = "" Then
            Cells(i, 1).EntireRow.Select
            Selection.Delete shift:=xlUp
        End If
    Next i
    Application.CutCopyMode = False[/COLOR]
    Application.ScreenUpdating = True
    End Sub
    
     
  5. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    HI,

    I think you also do it this way


    Code:
    Sub MoveData_ToColumn()
    Application.ScreenUpdating = False
    Dim lrow As Long
    Dim i As Long
    lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    For i = lrow To 2 Step -3
        Range("A" & i & ":C" & i).Copy Destination:=Range("B" & i - 1)
        Range("A" & i & ":C" & i).EntireRow.Delete
    Next
    End Sub
    
     
  6. maliaga

    maliaga Thread Starter

    Joined:
    Oct 6, 2011
    Messages:
    4
    Thank you very much!!! This works great!
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Don't forget the "Mark Solved" butoon :)
     
  8. maliaga

    maliaga Thread Starter

    Joined:
    Oct 6, 2011
    Messages:
    4
    Thanks Keebellah!!!
     
  9. 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/1021038

  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