Combine Columns in Macro

[Guest]

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!

 

[Tom Ogilvy]

Sub AA()
Dim rng as Range
Set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
rng.offset(0,2).Formula = "=a1&""""&b1"
rng.offset(0,2).formula = rng.offset(0,2).Value
rng.Resize(,2).Entirecolumn.Delete
end Sub

 

[Bernie Deitrick]

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

 

[Guest]

Tom, very helpful!!! Almost there. Your macro is deleting column C. I need
to keep C. Can you help?

 

[Tom Ogilvy]

In contrast, it ran fine for me and did exactly what you asked for: Once
again

Sub AA()
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 2).Formula = "=a1&""""&b1"
rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value
rng.Resize(, 2).EntireColumn.Delete
End Sub

Do you have merged cells?

 

[Bernie Deitrick]

Tom,

Your line:

rng.Offset(0, 2).Formula = "=a1&""""&b1"

overwrites the existing column C.

Perhaps

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.EntireColumn.Insert
rng.Offset(0, -1).Formula = "=B1&"" ""&C1"
rng.Offset(0, -1).Formula = rng.Offset(0, -1).Value
rng.Resize(, 2).EntireColumn.Delete


HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

He said Deletes - didn't realize he meant he had data in column C.
Thanks,

Sub AA()
Columns(3).Insert
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 2).Formula = "=a1&""""&b1"
rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value
rng.Resize(, 2).EntireColumn.Delete
End Sub

 

[Guest]

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

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

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!