Excel Macro Change Column when Cell = *

Microsoft Office excel 2007 home & stude...
November 1, 2010 at 03:48:34
Specs: Windows XP
I want a macro to solve the below problems please:
Sample:
A
B
C
T
*
D
E
F
G
*
A
B
*

Results:
A D A
B E B
C F
T G

So copy row 1 and when it reach to *, then change another column. The height is uneven so the only splitter is *
Many thanks for your help.


See More: Excel Macro Change Column when Cell = *

Report •


#1
November 1, 2010 at 17:36:21
re: So copy row 1 and when it reach to *...

I assume you mean "So copy column 1 and when it reach to *..."

With your data starting in A1, this code will split it, starting in B1:

Sub SplitData()
'Determine last Row with data in Column A
   lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Initialize Column number
    colNum = 2
'Loop through data
   For myList = 1 To lastRow
'When we hit an "*"...
   If Range("A" & myList) = "*" Then
'Increment loop counter so we don't use the "*"
     myList = myList + 1
'Increment the Column number
     colNum = colNum + 1
'Reset the Row number
     rowNum = 0
   End If
'Increment the Row number
     rowNum = rowNum + 1
'Place the data in the proper cell
     Cells(rowNum, colNum) = Cells(myList, 1)
   Next
End Sub


Report •
Related Solutions


Ask Question