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?
>Solution :
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.