VBA Transpose every nth row

[Nemo74]

Hello all, I'm glad to have found such a great place to learn about excel. I'm the kind of guy who learns from seeing the answer and working backwards. That being said here is my issue:

I one Sheet 1 I have the following:

Columns F2:R2 have values in them. I would like to copy those, transpose them to E2.
THEN SKIP the 13 rows and do the same thing to rows F16:R16, transpose to E17.

This needs to loop until it hits a blank cell. The whole sheet is about 3k rows with the blanks already waiting for the transpose.

I can't get it figured out.

Thanks in advance and I can't wait to see how this works out!

 

[Rick Rothstein]

I think this macro may do what you want...

Sub Tranpose13s()
  Dim Cell As Range
  On Error GoTo NothingInColumnF
  For Each Cell In Columns("F").SpecialCells(xlConstants)
    Cell.Offset(1, -1).Resize(13) = WorksheetFunction.Transpose(Cell.Resize(, 13))
  Next
NothingInColumnF:
End Sub

Note: My code assumes the values in Column F are constants. If those cells should contain formulas instead of constants, then change the constant I have highlighted in red to this... xlFormulas.

 

[JimM]

Try this code

Sub Macro3()

lastrow = Cells(Rows.Count, "F").End(xlUp).Row

For r = 2 To lastrow

Range("F" & r & ":R" & r).Select
Selection.Copy
If nextr = "" Then Range("E2").Select Else Range("E" & nextr).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Selection.End(xlDown).Select

nextr = ActiveCell.Row + 1


Next r

End Sub

 

[Nemo74]

I think this macro may do what you want...

Sub Tranpose13s()
  Dim Cell As Range
  On Error GoTo NothingInColumnF
  For Each Cell In Columns("F").SpecialCells(xlConstants)
    Cell.Offset(1, -1).Resize(13) = WorksheetFunction.Transpose(Cell.Resize(, 13))
  Next
NothingInColumnF:
End Sub

Holy cow! I NEVER figured it was that simple! Thank you so much, I'll file this away in the "You can learn from this exmaple" book!

Gosh thanks again.

 

[Rick Rothstein]

Holy cow! I NEVER figured it was that simple! Thank you so much, I'll file this away in the "You can learn from this exmaple" book!

You are quite welcome. Just so you know, I added the On Error protection just in case you ran the macro against a blank worksheet (or at least a sheet with nothing in Column F). If you know you will always run the macro against a sheet populated with your data, then you can simplify the macro by removing the On Error and its target Label...

Sub Tranpose13s()
  Dim Cell As Range
  For Each Cell In Columns("F").SpecialCells(xlConstants)
    Cell.Offset(1, -1).Resize(13) = WorksheetFunction.Transpose(Cell.Resize(, 13))
  Next
End Sub