Merge Two Columns Based on Header

**MSmithson** · 10-22-2010, 01:44 PM

Hello all,

Just need a little help writing a macro that will merge two columns together. The coliums marked “Retained Notes” and “Frozen Notes” need to be merged into one column simply marked “Notes.” The text (if any) in both columns needs to be merged into one cell in a single column with a space between the text. “Retained Notes” and “Frozen Notes” are the headers in row one.

Retained Notes Frozen Notes
Notes1 Notes2

Becomes:

Notes
Notes1 Notes2

**davegugg** · 10-22-2010, 03:07 PM

I don't think you need code for this. Assuming Retained Notes is in Column A and Frozen Notes is in Column B, Copy down column C:

=A2 & " " & B2

**MSmithson** · 10-22-2010, 03:10 PM

Thanks davegugg, this function is actually going to be used within a larger macro that performs several other functions with the merged columns, which is why I'm trying to automate the merging with a macro. Thanks.

**davegugg** · 10-22-2010, 03:26 PM

Ok, that makes good sense. Here is a code to use. You'll need to customize where the variables are initialized.

Public Sub MSmithson()

Dim strWS As String
Dim dblLastRow As Double
Dim intC1 As Integer
Dim intC2 As Integer
Dim intC3 As Integer
Dim intR1 As Integer
Dim i As Integer

'Initialize variables.
strWS = "Your Sheet Name"
intC1 = 1 'Enter the column number of Retained Notes (A = 1, B = 2, etc.)
intC2 = 2 'Enter the column number of Frozen Notes
intC3 = 3 'Enter the column number of Combined Notes
intR1 = 2 'Enter the row you want to start combining on.

With Sheets(strWS)
    dblLastRow = WorksheetFunction.Max(.Cells(Rows.Count, intC1).End(xlUp).Row, .Cells(Rows.Count, intC2).End(xlUp).Row)
    For i = 2 To dblLastRow
        .Cells(i, intC3).Value = .Cells(i, intC1).Value & " " & .Cells(i, intC2).Value
    Next i
End With

End Sub

**MSmithson** · 10-22-2010, 05:29 PM

Thanks for your reply davegugg! Retained Notes and Frozen Notes are always next to each other but sometimes in different columns (G, H or I). This is why I want to use the header in row one "Retained Notes" and Frozen" notes to identify these columns, then merge them into a new column. The merging would start at row 2.