Transpose macro

[Libby]

Hi,

I've been using this function to transpose records from a single column to 6
columns. All the records were 5 rows with a blank row separating each record
(total 6 rows).
=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

The latest set of records vary from 4 to 6 rows with a blank row separating
each record. How do I write a macro that adjusts to the number of rows of
each record (until it reaches a blank row) transposes it into the right
number of columns and then moves onto the next record for transposing?

Libby

 

[Bernie Deitrick]

Libby,

Try the macro below - it will start the transpose in column D - you can change that by changing the
value of myCol to the column # you want. I have also assumed that your blanks are truly blank, and
not "".....

HTH,
Bernie
MS Excel MVP


Sub TransposeCells()
Dim myData As Range
Dim myArea As Range
Dim i As Integer
Dim myRow As Long
Dim myCol As Integer

myCol = 4

Set myData = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants)

For Each myArea In myData.Areas
myRow = Cells(Rows.Count, myCol).End(xlUp)(2).Row
For i = 1 To myArea.Cells.Count
Cells(myRow, i + myCol - myData.Column).Value = myArea.Cells(i).Value
Next i
Next myArea

End Sub