CODEWITHSUNDEEP
vbaexcelrowmultiple-columns
Skirmante Valentaite
Skirmante Valentaite

Reputation: 105

Excel data from colum to row

I have data like in the picture below:

enter image description here

I need to (with a macro) arrange the data so that every row with a number after ELEVATION\AZIMUTH be in the first row, like in picture:

enter image description here

I have a lot of rows like this data. Maybe any one can help?

Upvotes: 1

Views: 107

Answers (2)

Srikanta Gouda
Srikanta Gouda

Reputation: 521

This is not tested. you can give it a try. the below can be written in diff way as well.

  Sub test1()
        Dim LastRow, DataCount, temp  As Double
        i = 1
        LastRow = 1

        Do While LastRow <> 0
            Range("A" & i).Select
            If ActiveCell.Value = "ELEVATION\AZIMUTH" Then
                'Cut all three row and paste
                DataCount = Application.WorksheetFunction.CountA(Range(i & ":" & i))
                Range("A" & ActiveCell.Row + 1, "I" & ActiveCell.Row + 1).Cut ActiveCell.Offset(0, DataCount)
                Range("A" & ActiveCell.Row + 2, "I" & ActiveCell.Row + 2).Cut ActiveCell.Offset(0, DataCount * 2)
                Range("A" & ActiveCell.Row + 3, "I" & ActiveCell.Row + 3).Cut ActiveCell.Offset(0, DataCount * 3)
            Else
                LastRow = Application.WorksheetFunction.CountA(Range("A" & i, "A" & i + 10))
            End If
            i = i + 1
        Loop

    End Sub

Upvotes: 2

harinp715
harinp715

Reputation: 41

This can also be done without the use of macros. I am assuming your last column where data is there is column I and the row number is 11. You want to fill all cells in row 11 after column I, i.e. J11,K11.... with values right below I11

You could do this, paste in J11 J11=INDEX($I$11:$I$1000,COLUMN(B1),1) Drag the formula across the row and you should get your desired output

Upvotes: 2

Related Questions