In this post we learn to stack or combine multiple columns into a single column in Excel using VBA macros. Depending on the scenario, that’s something you can probably do without macros, but in some cases, and especially when dealing with a lot of data, is more convenient, faster, and more secure to do it with macros. This is a simple macro to stack the selected columns into one column either in the same or a different worksheet. The macro can easily be adapted to stack the columns skipping the headers by changing just one line of code (see more in the explanation below).
Macro/VBA code:
Sub
StackColumns()
Dim
stackedCol As Range, targetCol As Range, lr As Long
With
ActiveSheet 'change if need to add to other
sheet
Set stackedCol = .Columns("X") 'Change X with your recipient column
If Selection.Columns.Count > 1 Then
For Each targetCol In Selection.Columns.EntireColumn
targetCol.SpecialCells(xlCellTypeConstants).Copy
lr = .Cells(Rows.Count, stackedCol.Column).End(xlUp).Row
.Cells(lr + 1, stackedCol.Column).PasteSpecial
Next targetCol
Else
MsgBox "Select multiple columns"
End If
End
With
End Sub
Macro explained:
- First we declare two variables. The first is stackedCol and is the recipient single column to consolidate the data in the other columns (columns in the selection). It is an object variable declared as a Range. The other is targetCol, also declared as a Range, and represents each column while looping through the columns in the selection. We also declare lr to store the last row with content in the stacked column while looping.
- Then we open a With statement for the active sheet. This is the recipient worksheet to have the stacked column, but can be changed to any other sheet with the appropriate reference (for example, With Sheets(“Sheet2”).
- After that, we define the stacked column in that worksheet. IMPORTANT: Note that the code refers to column X. You need to change this to the column where you want to stack or combine the data in all the other selected columns.
- Before starting the loop, there is a condition to check if the selection contains multiple columns, otherwise, it makes no sense to go through the process. That’s obtained with Selection.Columns.Count.
- Then we start a For Each loop through each column (targetCol) in the selected columns. Note that we refer to the entire column here, so that regardless of the selection, it will get the contents from the entire column. If you want to target the selected cells only, remove the property “EntireColumn”.
- Now we can copy the contents in that column (targetCol) using the SpecialCells method to skip the empty cells. It’s also possible to do it without SpecialCells, but then it would copy the entire column (1M+ rows) and take much longer. If we want to skip the headers, then we need to modify this line of code and specifically refer to the range spanning from the second row to the last row with content as follows:
Range(Cells(2, targetCol.Column), _
Cells(targetCol.SpecialCells(xlCellTypeLastCell).Row, targetCol.Column)).Copy
- We need to paste the data in the new stacked column after the last row with content, which is assigned to the variable “lr” using the End and Row properties for that column to get the last row with content.
- Finally, we can paste the data in the new stacked column, starting on the cell after the last row with content (which is the first empty cell) in that column. Thus, we are pasting the data in each column in the selection, one after another for each loop, to stack or combine all of them into a single column. Note that we are using the method PasteSpecial, which allows to specifically paste the values, formats, etc, if we need to make that differentiation.
This is how we stack combine multiple columns in Excel with VBA macros.
Other examples:
- How to Strike Through Text In Cell with Excel VBA macros
- How to Read API JSON Response with Excel VBA macros
- How to Send HTTP Request to Connect to API with Excel VBA macros
No comments:
Post a Comment