thanks it's really work very well thanks again.
tufail

"Leith Ross" wrote:


Hello Tufail,

Here is another version. This macro lets you state the source cell of
the rows to convert, the destination cell where the converted rows will
be placed, and the number of columns. The MyMacro code calls the
RowsToColumns macro and supplies the parameters to it. The macro will
calculate the last used cell in the source row, so you don't need to
select the range or enter the start and stop cells in the range.

Add a VBA module to your code and paste the macro code into it. You can
then run the macro manually using the macro dialog in Excel. Type ALT+F8
to bring up the macro dialog while in Excel.


Code:
--------------------

Sub RowsToColumns(SrcRng As Range, DstRng As Range, ColumnsPerRow As Integer)

Dim DstWks As Worksheet
Dim SrcWks As Worksheet

Set SrcWks = Worksheets(SrcRng.Parent.Name)
Set DstWks = Worksheets(DstRng.Parent.Name)

DestCol = DstRng.Column
DestRow = DstRng.Row
SrcCol = SrcRng.Column
SrcRow = SrcRng.Row
LastRow = SrcWks.Cells(Rows.Count, SrcCol).End(xlUp).Row

For J = SrcRow To LastRow Step ColumnsPerRow
For I = 0 To ColumnsPerRow - 1
CurrentCell = SrcWks.Cells(I + J, SrcCol).Value
SrcWks.Cells(I + J, SrcCol).ClearContents
DstWks.Cells(DestRow, DestCol + I) = CurrentCell
Next I
DestRow = DestRow + 1
Next J

End Sub

Sub MyMacro()

Call RowsToColumns(Range("A1"), Range("A1"), 5)

End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557668