Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose macro
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(xlCellTypeCo nstants) 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 "Libby" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose or Macro? | Excel Discussion (Misc queries) | |||
Transpose macro | Excel Programming | |||
Transpose macro | Excel Programming | |||
help with transpose-like macro | Excel Programming |