Need help with restructuring Macro Columns to rows, Please!!!

[estatefinds]

I have data that is in column C that is numeric with dashes. I need the macro Columns to rows to be able to take this data that is found in the cell and move it to the Row.
Currently the macro on works with just numeric data, but not numeric data with "-" in between the numerica data.
So I need the macro to be able to do what it suppossed to as it does now but be able to work with the data in column C you ll find on the excell sheet that I attach.

Thank you very much!!! in advance

Sincerely Dennis

Sub movetocolumns()Dim i As Integer, iRow As Integer
Dim arrSource As Variant


'Set the first row
iRow = 1


With ActiveWorkbook.Worksheets("Sheet1")
    'get the data into an array from the first column
    arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))


    'parse every value of the array and add the data to the next column
    For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 3) Step 3
        .Cells(iRow, 2) = arrSource(i, 1)
        .Cells(iRow, 3) = arrSource(i + 1, 1)
        .Cells(iRow, 4) = arrSource(i + 2, 1)
        iRow = iRow + 1
    Next i
    'add the remaining values
    Select Case UBound(arrSource) Mod 3
        Case 1  'one item to add
            .Cells(iRow, 2) = arrSource(i, 1)
        Case 2  'still two items to add
            .Cells(iRow, 2) = arrSource(i, 1)
            .Cells(iRow, 3) = arrSource(i + 1, 1)
        Case Else   'nothing to add
    End Select
End With
End Sub

[mdmackillop]

Can you not do this with Data/Text to columns?

[estatefinds]

I Prefer the macro to do this as it does work great and fast, please
Thank you

[mdmackillop]

Sub Test()
  Range("C1").CurrentRegion.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, OtherChar:="-"
End Sub

[estatefinds]

i ran what you had posted and nothing happends?

[mdmackillop]

Try the unedited version

Sub Macro4()
    Range("C1").CurrentRegion.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1)), TrailingMinusNumbers:=True
End Sub

[Paul_Hossler]

One problem is that Column A has no data, so the array is empty

  arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))

Another is that it is not very clear what you'd like the result to be formatted as

Please add an 'After' worksheet to your attachment

[mdmackillop]

Hi Paul
He asks for the code to be applied to Column C

[estatefinds]

that unmerges,
All i need is the data in the column C moved to rows. In post 3 the example shows what it looks like disregrd the hight lighted red.
so the data needs to be moved from column to rows with out altering the data that is in column C

[mdmackillop]

Just change the destination to E1. I can't follow what the Post 3 example is intended to show; there seems no relation between column C and thae other data.

[estatefinds]

sorry i forgot to add that data to worksheet, here it is Thankyou

[estatefinds]

ok im sending file of what is suppossed to look like. the data will be moved from column C to rows starting a E1 to AL1 then resume placing data to E2 to AL2 and continue until all data is placed.


the data in column C is in order from top to bottom, so the data will be placed in rows starting at E1.

[estatefinds]

review post 12 and attachment. thank you

[mdmackillop]

Sub Test()
Set r = Range("C1").CurrentRegion
rw = r.Rows.Count
Set t = Cells(1, 5).Resize(1 + rw \ 34, 34)
For i = 1 To r.Rows.Count
t.Cells(i) = r(i)
Next
End Sub

[estatefinds]

it worked great thank you very much!!!!