Combine Specific Table Columns

Question

I am looking to combine specific columns in a table, specified by the column header, into one column outside of the table. So far I have the below script that works okay except that it combines adjacent columns only and the column numbers are static.

I would like to develop the script so that it works with non-continuous ranges based the column header names. I was going to use a helper column to list the column headers to combine. The below screenshot shows an example where in the helper column H three column headers have been listed (in reality the number of column headers listed will vary) and based on this the data in those columns have been combined to form a new consolidated list in column J. I would like to achieve this using VBA rather than Power Query due to earlier versions of Excel.

Sub combine()

Dim LR As Long, i As Long

For i = 1 To 6
    LR = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(2, i), Cells(LR, i)).Copy _
        Destination:=Cells(Rows.Count, 10).End(xlUp).Offset(1)
Next i

End Sub

L42 · Accepted Answer · 2018-03-28 03:49:31Z

2

You can try this provided you set up your source tables.
Pre-requisite: Create there(3) tables, named RawData, Helper and Combined.
Where:

RawData is where your data lies
Helper (a single column table) is the list of headers you want to combine
Combined (a single column table) is the list of combined items from selected columns

Sub terrain()
    Dim rD As ListObject, cT As ListObject, hT As ListObject
    Dim c As Range

    With Sheet1 '/* change to your actual sheet name or sheet code name */

        Set rD = .ListObjects("RawData")
        Set cT = .ListObjects("Combined")
        Set hT = .ListObjects("Helper")

        With rD
            On Error Resume Next
            cT.DataBodyRange.Delete xlUp
            On Error GoTo 0

            If Not hT.DataBodyRange Is Nothing Then
                For Each c In hT.DataBodyRange
                    If cT.DataBodyRange Is Nothing Then
                        On Error Resume Next
                        .ListColumns(c.Value2).DataBodyRange. _
                        SpecialCells(xlCellTypeConstants).Copy _
                        cT.HeaderRowRange.Offset(1, 0)
                        On Error GoTo 0
                    Else
                        On Error Resume Next
                        .ListColumns(c.Value2).DataBodyRange. _
                        SpecialCells(xlCellTypeConstants).Copy _
                        cT.DataBodyRange.Range("A" & cT.ListRows.Count + 1)
                        On Error GoTo 0
                    End If
                Next
            End If
        End With

    End With

End Sub

If however, the column names you provided in your Helper table does not exist, this will result to Subcript out of range error. What I did is not so neat enclosing the copy part with On Error Resume Next and On Error Goto 0.

edited Mar 28, 2018 at 3:49

answered Mar 28, 2018 at 3:29

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3673417 Over a year ago

If I wanted to extend the functionality so that it would pickup a quantity column for each Feature would this be possible? ie if in the raw data table I inserted a new column between each existing Feature column, naming them as Feature1_Qty etc would it be possible to copy the data in the pair of columns to the combined table (which would now have 2 columns). The number of Feature_Qty rows = the associated feature column rows. I assume it would mean introducing something like .ListColumns(c.Value2 & "_Qty").DataBodyRange.SpecialCells(xlCellTypeConstants).Copy _

L42 Over a year ago

@user3673417 Yes it is possible but not the way you described it in your comment but a bit simpler

user3673417 Over a year ago

Just solved it by using resize, .DataBodyRange.Resize(, 2). Thank you for your help

L42 Over a year ago

@user3673417 Yes, exactly :)

user4039065 · Accepted Answer · 2018-03-28 05:23:56Z

-1

Your problem was intriguing but this is largely untested because, quite frankly, I have better things to do with my time than retype your sample data from an image. Happily, it does compile however. Well, it will compile just as soon as you retype the code from my image.

answered Mar 28, 2018 at 5:23

user4039065

Collectives™ on Stack Overflow

Combine Specific Table Columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related