VB code required for arrange row data into columns in fastest way

[Nitesh Mathur]

Hi,

Im using MS Excel 2007 and required VB code for arranging rows in table in specified format and also fastest way.
Excel file is attached for reference.

My data table is in “Data” sheet and sheet “Result” in which macro can populate rows into columns.

But my problem is that my columns are 60 and rows are 350. I need a fastest way to arrange the same because the macro which I have written takes approx 45-60 minutes to populate data into Result sheet.

Macro is as shown below:

Sub Arrange_Data()

Dim rng As Range, cell As Range, sht As Worksheet, i As Double, Cl1 As String, Cl2 As String

Cl1 = Sheets("Sheet1").Range("A2").Value
Cl2 = Sheets("Sheet1").Range("B2").Value

Set sht = Sheets("result")
Set rng = Sheets("Data").Range(Cl1 & ":" & Cl2)
Sheets("Data").Select

i = 2
For Each cell In rng
sht.Cells(i, 1) = Cells(cell.Row, 1).Value
sht.Cells(i, 2) = Cells(cell.Row, 2).Value
sht.Cells(i, 3) = Cells(1, cell.Column).Value
sht.Cells(i, 4) = cell.Value
i = i + 1
Next cell

End Sub

Thanks in advance
Nitesh

 

[Marc L]

Hi !

Desactivate ScreenUpdating, see in VBA inner help …
​