VBA: How can I organize a 100 columns table into a one-column list with 2 empty rows between the it-CodePudding

I have a table with 100 columns, each column has 8 rows. Example:

Column 1	Column 100
Abc	123
Def	456
Abc	123
Def	456
Abc	123
Def	456
Abc	123
Def	456

I would like to organize the 100 columns table into one single column with two empty rows between the items. The result should be as follows:

Column 1
Abc
Def
Abc
Def
Abc
Def
Abc
empty
empty
123
456
123
456
123
456
123
456

Any suggestions on how to code it?

CodePudding user response：

Please, try the next code. It does not use Clipboard and it should be fast enough. You can configure the number of columns to be processed. The code itself determines the number of rows in "A:A" and assumes that al the other columns have the same number of rows:

Sub MergeColumnsContent()
  Dim sh As Worksheet, lastR As Long, lr As Long, lastColNo As Long, i As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
      lr = lastR   'memorize the initial number of rows (in A:A), which must be the same for all 100 columns
  lastColNo = 17   'last column to be processed (merged)
  
  For i = 2 To lastColNo
        With sh.Range(sh.cells(1, i), sh.cells(lastR, i))
            sh.Range("A" & lastR   3).Resize(.rows.count, 1).Value = .Value
        End With
        lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'recalculate the last row, after copying
  Next i
  'sh.Range("B1", sh.cells(lr, lastColNo)).ClearContents 'uncomment if you want automatically clear the contents of the processed columns
End Sub

After running the code and check if it does exactly what you need, you can (now) manually clear the contents of the processed columns. Or clear everything in A:A, except the first 8 rows, uncomment the last code line and run the code again. Now it will automatically clear the processed columns content (2 to 100)...

Please, send some feedback after testing it.