Combine Columns in Macro
 

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!

Combine Columns in Macro
 

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



"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!

Combine Columns in Macro
 

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!

Combine Columns in Macro
 

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

"Tom Ogilvy" wrote:

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



"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!

Combine Columns in Macro
 

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?

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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



"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!

Combine Columns in Macro
 

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" wrote in message ...
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?

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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



"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!

Combine Columns in Macro
 

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

--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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



"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!

Combine Columns in Macro
 

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!