I would like to repeat a macro until no data is found.

I am pulling data from a column and transposing the data into rows. The
data repeats after ten rows and than I have to start a new row to transpose.
I have created a macro that transposes the data, but I want to repeat the
macro until no other data is found. Any advice would be appreciated.

Try this one, adjust the Const values as required. As written, it will take
the information in column A and move it in groups of 10 using columns C:L
beginning at row 2.

Sub TransposeColumnToRows()
'column the source information to be
'transposed is in
Const sourceCol = "A"
'first row with a data item to be
'transposed
Const firstDataRow = 1
'the size of the groups to be
'transposed
Const groupSize = 10
'the first column to use for
'transposing the data
Const newCol = "C"

Dim lastRow As Long
Dim RC As Long
Dim CC As Integer
Dim rowPointer As Integer

lastRow = Range(sourceCol & Rows.Count).End(xlUp).Row
For RC = firstDataRow To lastRow Step groupSize
rowPointer = Range(newCol & Rows.Count).End(xlUp).Row + 1
For CC = 0 To groupSize - 1
Range(newCol & rowPointer).Offset(0, CC) = _
Range(sourceCol & RC).Offset(CC, 0)
Next
Next
End Sub

To put the code to work, open the workbook, press [Alt]+[F11] to open the VB
Editor and choose Insert -- Module. Copy the code and paste it into the
module and make any changes you need to. Close the VB Editor. Select the
sheet with the data to transpose and use Tools -- Macro -- Macros to run
the code.

"dwake" wrote:

I would like to repeat a macro until no data is found.

I am pulling data from a column and transposing the data into rows. The
data repeats after ten rows and than I have to start a new row to transpose.
I have created a macro that transposes the data, but I want to repeat the
macro until no other data is found. Any advice would be appreciated.

Worked perfect....much abliged.

"JLatham" wrote:

Try this one, adjust the Const values as required. As written, it will take
the information in column A and move it in groups of 10 using columns C:L
beginning at row 2.

Sub TransposeColumnToRows()
'column the source information to be
'transposed is in
Const sourceCol = "A"
'first row with a data item to be
'transposed
Const firstDataRow = 1
'the size of the groups to be
'transposed
Const groupSize = 10
'the first column to use for
'transposing the data
Const newCol = "C"

Dim lastRow As Long
Dim RC As Long
Dim CC As Integer
Dim rowPointer As Integer

lastRow = Range(sourceCol & Rows.Count).End(xlUp).Row
For RC = firstDataRow To lastRow Step groupSize
rowPointer = Range(newCol & Rows.Count).End(xlUp).Row + 1
For CC = 0 To groupSize - 1
Range(newCol & rowPointer).Offset(0, CC) = _
Range(sourceCol & RC).Offset(CC, 0)
Next
Next
End Sub

To put the code to work, open the workbook, press [Alt]+[F11] to open the VB
Editor and choose Insert -- Module. Copy the code and paste it into the
module and make any changes you need to. Close the VB Editor. Select the
sheet with the data to transpose and use Tools -- Macro -- Macros to run
the code.

"dwake" wrote:

I would like to repeat a macro until no data is found.

I am pulling data from a column and transposing the data into rows. The
data repeats after ten rows and than I have to start a new row to transpose.
I have created a macro that transposes the data, but I want to repeat the
macro until no other data is found. Any advice would be appreciated.