Transpose At Every Blank Row

[sasuke001]

I was wondering if a macro can be created to transpose data that is in a column at the end every blank row. Into new cells in a row.

A1
A2
A3

A4
A5

A6
A7
A8
A9

becomes
A1 | A2 | A3
A4 | A5
A6 | A7 | A8 | A9

 

[VoG]

Hello and welcome to MrExcel.

I suspect that there may be a much smarter way but try this.

Sub Xpose()
Dim lastrow As Long, i As Long, j As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With Sheets("Sheet1") '<<<<<<<<<<<<<<<<<<<< change to suit
    Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    iStart = 1
    For i = 1 To lastrow + 1
        If .Range("A" & i).Value = "" Then
            iEnd = i
            j = j + 1
            .Range(.Cells(iStart, 1), .Cells(iEnd, 1)).Copy
            ws.Range("A" & j).PasteSpecial Paste:=xlPasteValues, Transpose:=True
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Change Sheet1 to the name of your sheet. A new sheet will be added containing the transposed values.

 

[Peter_SSs]

Welcome to the MrExcel board!

Not much detail here about which column the data is in or where the results are to go, but see if this is some help. It assumes...
- Data is in column A
- Results to go into column c and to the right, starting from row 1
- data is constants, not formula results.

Sub TransposeAreas()
  Dim aArea As Range
  Dim nr As Long
  
  Application.ScreenUpdating = False
  nr = 1
  For Each aArea In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    aArea.Copy
    Cells(nr, 3).PasteSpecial Transpose:=True
    nr = nr + 1
  Next aArea
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[sasuke001]

Thank you guys very much. VoG's code did exactly what I wanted, thanks again. Sorry, Peter SSs, I was already done after using VoG's code.

 

[Weaver]

Well first come, first served I guess, but I'd have to say I prefer Peter's - areas...now there's a new and interesting concept...

 

[aadiimughal]

where and how to add this code?

 

[Peter_SSs]

where and how to add this code?

Welcome to the MrExcel board!

Try these steps for one way to do it:
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro you want & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[aadiimughal]

you are great. thanks

 

[Peter_SSs]

You're welcome. Glad it helped.