merge 2 columns

**syn** · 09-12-2008, 02:46 PM

Hey guys i was wandering if you could help, i am trying to create a macro that merges two columns in excel.

i have this vba script

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/12/2008 by jking
'
' Keyboard Shortcut: Ctrl+s
'
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&""""""""&RC[-1]"
    Range("I3").Select
End Sub

but it only works on row 2 and none of the others. The spread sheet can contain a variable amount of rows in these two columns so i created what i thought would work a range specifier.

Set rng = Range(Cells(1, 9), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 2).Formula = "=G2&""""&H2"
rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value
rng.Resize(, 2).EntireColumn.Delete

but when i did that it didn't do anything. can anyone help.