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!

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" <Rob@discussions.microsoft.com> wrote in message
news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
> 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!

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" <Rob@discussions.microsoft.com> wrote in message
news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
>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, 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" <Rob@discussions.microsoft.com> wrote in message
> news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
> > 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!
>
>
>

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" <Rob@discussions.microsoft.com> wrote in message
news:E6DE1D87-D2EE-42ED-9C37-5BCB793C95B9@microsoft.com...
> 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" <Rob@discussions.microsoft.com> wrote in message
> > news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
> > > 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,

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" <twogilvy@msn.com> wrote in message news:u8fVmQfmFHA.764@TK2MSFTNGP14.phx.gbl...
> 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" <Rob@discussions.microsoft.com> wrote in message
> news:E6DE1D87-D2EE-42ED-9C37-5BCB793C95B9@microsoft.com...
>> 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" <Rob@discussions.microsoft.com> wrote in message
>> > news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
>> > > 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!
>> >
>> >
>> >
>
>

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
news:e$lH6WfmFHA.2860@TK2MSFTNGP15.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
news:u8fVmQfmFHA.764@TK2MSFTNGP14.phx.gbl...
> > 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" <Rob@discussions.microsoft.com> wrote in message
> > news:E6DE1D87-D2EE-42ED-9C37-5BCB793C95B9@microsoft.com...
> >> 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" <Rob@discussions.microsoft.com> wrote in message
> >> > news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
> >> > > 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 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" <Rob@discussions.microsoft.com> wrote in message
> news:4FD44ADC-3418-43E1-AA40-9EF65DFDEA62@microsoft.com...
> >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!
>
>
>