0

I'm on the last leg of this script and having an issue with my output. I have a variable # of columns and I'm trying to transpose/paste the columns into one column below the table sans the title row.

Title   Title
1       3  ===== > =======  1
2       4  ===== > =======  2
                            3
                            4

My for statement

For iCol = 1 To rng.Columns.Count

    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 3)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count

Next iCol

The issue I'm having is that the paste is overwriting itself.

The full statement for reference: 

Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Dim secondlCol As Long
'///////////////////////////////////////////////////////
secondlRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

    'Find the last non-blank cell in row 1
    secondlCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column

    theActiveCell = "B4:" & Col_Letter(secondlCol) & "5"
    theActiveCell = theActiveCell
    theActiveCellSel = ActiveSheet.Range(theActiveCell).Select
'///////////////////////////////////////////////////////
Set rng = Range(theActiveCell).CurrentRegion
lastCell = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column

For iCol = 1 To rng.Columns.Count

    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 3)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count

Next iCol

Note: The illustration shows the output to the right, but it will appear below the table. Tough to form that in the editor here.

Community
  • 1
  • 1
user3135730
  • 320
  • 1
  • 3
  • 8
  • 1
    Why not paste _somewhere else_ and then copy back if necessary? – ashleedawg Jan 01 '18 at 10:45
  • I don't understand exactly what you want to do: (1) Do you want to fill all data in *one* single column immediately below your current region in column A? (2) Do you want to omit empty values? Would be the best to answer by editing your original post. - BTW, apparently you forgot to show the function code of `Col_Letter` (possibly from this post: https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter?noredirect=1&lq=1). – T.M. Jan 01 '18 at 20:13

0 Answers0