+ Reply to Thread
Results 1 to 3 of 3

Arbitrary points: Split single column into multiple columns

Hybrid View

ChasingShadows Arbitrary points: Split... 07-23-2012, 07:21 PM
jindon Re: Arbitrary points: Split... 07-23-2012, 09:11 PM
ChasingShadows Re: Arbitrary points: Split... 07-23-2012, 11:08 PM
  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Angry Arbitrary points: Split single column into multiple columns

    Hello! I am new to Excel VBA and need major help for my code.

    Please refer to my picture and the excel sheet data below as reference.

    GIVEN:
    A 4-column list of data points.
    There are an arbitrary number of sets, defined by an existing number in column A, followed by blank cells in A but "x,y,z" points in B, C, D.
    Each set contains an arbitrary amount of points.

    OBJECTIVE:

    I'd like to parse the single columns into multiple columns, but each column contains an arbitrary number of points.

    The picture provided shows what I'd like to do, please look at that for a clearer picture.

    PROBLEM:
    My code is not working, it only took 2 cells and placed it to the side.
    Is there a problem with the loop? I can't seem to figure it out...

    Please help! Thank you, much appreciated!

    row = 2 'start at row 2
    col = 2 'start at column B
    
    
    Do While IsEmpty(Cells(row, 2)) = False                    'Keep running code if 2nd column is not empty
    
    
    If IsEmpty(Cells(row, 1)) = False Then                     'If column A is not empty, move over to new column
        Cells(row, 2).Select
        Selection.Cut Destination:=Cells(row, col + 2)  'Take column B and move over 2 columns
        Cells(row, 3).Select
        Selection.Cut Destination:=Cells(row, col + 3)  'Take column C and move over 2 columns
        col = col + 2                                          'Move across 2 columns
    
    
    Else
        Cells(row, 2).Select
        Selection.Cut Destination:=Cells(row, col + 2) 'Take row and move to the current column
        Cells(row, 3).Select
        Selection.Cut Destination:=Cells(row, col + 3)  'Take column C and move over 2 columns
        col = col + 2                                          'Move across 2 columns+ 1, col))
        row = row + 1                                         'Move down a row
    
    End If
    Loop
    
    
    End Sub

    ColumnMove.png
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Arbitrary points: Split single column into multiple columns

    Is this what you wanted?
    
    Sub test()
        Dim myAreas As Areas, myArea As Range, n As Long
        With Range("a1", Range("a" & Rows.Count).End(xlUp))
            Set myAreas = .SpecialCells(4).Areas
        End With
        n = 5
        For Each myArea In myAreas
            With myArea
                .Offset(-1, 1).Resize(.Rows.Count + 1, 3).Copy Cells(1, n)
                n = n + 4
            End With
        Next
        Application.CutCopyMode = False
    End Sub

  3. #3
    Registered User
    Join Date
    07-20-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Arbitrary points: Split single column into multiple columns

    Thank you so much! This worked perfectly. I did do some tweaks to clean the data up.
    Used cut instead of copy and offset(-1,0) to grab the values in "a" column.

+ 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