Excel Combine columns depending on header name using VBA
I have seen many times that people ask questions related to this topic, below are some examples
1) To copy specific columns and paste to another workbook
2) To distribute reports from a master table
2) To group specific columns to apply the same formatting
In this tutorial, I will explain how to combine columns using different methods.
Example 1 – when column name or number is unknown
Scenario: In a worksheet called “master”, assume that we need to search any header in row 1 that contains “2014”, then group all columns together.
Dim masterWS As Worksheet Set masterWS = Worksheets("master") rowLastColNum = masterWS.Range("IV" & 1).End(xlToLeft).Column For c = 1 To rowLastColNum If InStr(Cells(1, c).Value, "2014") <> 0 Then colName = Split(Cells(1, c).Address, "$")(1) combinedColName = combinedColName & colName & ":" & colName & "," End If Next c Set combinedRng = Range(Left(combinedColName, Len(combinedColName) - 1))
Explanation
1. Use Instr Function to loop through each Cell in row 1 to see which Cell contains “2014”
2. Convert column number (1,2,3,4) to column name (A,B,C,D) using Address Property and Split Function.
3. For each loop of column and if “2014” is matched, turn the column name to A:A and add a comma behind. For example, if you found column A and B contain “2014”, you will have A:A,B:B,
4. At the end of the loop, remove the comma and then end of A:A,B:B, using Right Function.
5. Set a range called combinedRng for Range(A:A,B:B)
Example 2 – when column name or number is known
Scenario: In a worksheet called “master”, assume that we already know the columns we need to group is A,B,C,E and we want to combine them into one Range
Dim masterWS As Worksheet Set masterWS = Worksheets("master") Set combinedRng = masterWS.Range("A:C, E:E")
Example 3 – when Range Object is known
Scenario: Assume that we have already set three Range Objects called RangeA, RangeB, RangeE and we want to combine them into one Range
Set combinedRng = Union(RangeA, RangeB, RangeE)
What to do next?
After you have combined the Range, you can copy the data to a new workbook
combinedRng.Copy Set newbook = Workbooks.Add ActiveCell.PasteSpecial Paste:=xlPasteValues With newbook .Title = “This title is displayed in Info > Properties” .Subject = “This subject is displayed in Info > Properties” .SaveAs Filename:=”C:\Users\WYMAN\Desktop\New workbook.xlsx” .Close End With