VBA Excel combine columns depending on header name

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

Outbound References

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/extract-columns-from-excel-to-new-sheet-as/d825f54f-4f19-42da-881b-95ae0af74b04

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *