Tom and Bernie,
Never did I expect such great help! Thank you Thank you Thank you!!!

"Bernie Deitrick" wrote:

Sub CombineForRob()
With Range("A1", Range("A65536").End(xlUp))
.EntireColumn.Insert
With .Offset(0, -1)
.FormulaR1C1 = "=RC[1]&"" "" &RC[2]"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With
Range("B:C").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"Rob" wrote in message
...
I need to combine column a and b in a macro that I can run on multiple
worksheets with different amounts of rows. I believe I need to create a new
column, paste the combined values using (=a1&""&b1), in the new column, and
then delete the two old columns.

This I can do, but my problem comes when I try to run the same macro on a
worksheet that contains more rows then the original one. There must be a way
to do this.

For instance, if there are 100 columns in the orginal worksheet where I
created the macro, then if there are 120 columns, the macro is only combining
1-100, 101- 120.

Please help a poor frustrated soul!