Optimize simple macro

Hi folks!

I have this simple macro that was created using the recorder.

Sub foo()

Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
Range("A1").Select

End Sub

It simply inserts a new row 2 and places a concatenation formula in cell G2.

How can this be optimized? I know SELECTS are undesireable but I'm more
interested in how to get the formula in A1 reference style.

Thanks!

Biff

modify to suit your cell addresses
Sub foo1()
Rows(2).Insert
Cells(2, "g").Formula = "=a2&a3&a4"
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi folks!
>
> I have this simple macro that was created using the recorder.
>
> Sub foo()
>
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
> Range("A1").Select
>
> End Sub
>
> It simply inserts a new row 2 and places a concatenation formula in cell
G2.
>
> How can this be optimized? I know SELECTS are undesireable but I'm more
> interested in how to get the formula in A1 reference style.
>
> Thanks!
>
> Biff
>
>

Thanks, Don.

Biff

"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> modify to suit your cell addresses
> Sub foo1()
> Rows(2).Insert
> Cells(2, "g").Formula = "=a2&a3&a4"
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi folks!
>>
>> I have this simple macro that was created using the recorder.
>>
>> Sub foo()
>>
>> Rows("2:2").Select
>> Selection.Insert Shift:=xlDown
>> Range("G2").Select
>> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
>> Range("A1").Select
>>
>> End Sub
>>
>> It simply inserts a new row 2 and places a concatenation formula in cell
> G2.
>>
>> How can this be optimized? I know SELECTS are undesireable but I'm more
>> interested in how to get the formula in A1 reference style.
>>
>> Thanks!
>>
>> Biff
>>
>>
>
>

Try

ActiveCell.Formula = "=B2&C2&D2&E2&F2"


Regards,

Peo Sjoblom

"Biff" wrote:

> Hi folks!
>
> I have this simple macro that was created using the recorder.
>
> Sub foo()
>
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
> Range("A1").Select
>
> End Sub
>
> It simply inserts a new row 2 and places a concatenation formula in cell G2.
>
> How can this be optimized? I know SELECTS are undesireable but I'm more
> interested in how to get the formula in A1 reference style.
>
> Thanks!
>
> Biff
>
>
>

glad it helped


--
Don Guillett
SalesAid Software
[email protected]
"Biff" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Don.
>
> Biff
>
> "Don Guillett" <[email protected]> wrote in message
> news:%[email protected]...
> > modify to suit your cell addresses
> > Sub foo1()
> > Rows(2).Insert
> > Cells(2, "g").Formula = "=a2&a3&a4"
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi folks!
> >>
> >> I have this simple macro that was created using the recorder.
> >>
> >> Sub foo()
> >>
> >> Rows("2:2").Select
> >> Selection.Insert Shift:=xlDown
> >> Range("G2").Select
> >> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
> >> Range("A1").Select
> >>
> >> End Sub
> >>
> >> It simply inserts a new row 2 and places a concatenation formula in
cell
> > G2.
> >>
> >> How can this be optimized? I know SELECTS are undesireable but I'm more
> >> interested in how to get the formula in A1 reference style.
> >>
> >> Thanks!
> >>
> >> Biff
> >>
> >>
> >
> >
>
>

> interested in how to get the formula in A1 reference style.

Would this idea work? HTH :>)

Sub foo()
Rows(2).Insert
Range("G2").Formula = "=B2&C2&D2&E2&F2"
Range("A1").Select
End Sub

--
Dana DeLouis
Win XP & Office 2003


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi folks!
>
> I have this simple macro that was created using the recorder.
>
> Sub foo()
>
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
> Range("A1").Select
>
> End Sub
>
> It simply inserts a new row 2 and places a concatenation formula in cell
> G2.
>
> How can this be optimized? I know SELECTS are undesireable but I'm more
> interested in how to get the formula in A1 reference style.
>
> Thanks!
>
> Biff
>
>

Thanks, Peo.

Biff

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Try
>
> ActiveCell.Formula = "=B2&C2&D2&E2&F2"
>
>
> Regards,
>
> Peo Sjoblom
>
> "Biff" wrote:
>
>> Hi folks!
>>
>> I have this simple macro that was created using the recorder.
>>
>> Sub foo()
>>
>> Rows("2:2").Select
>> Selection.Insert Shift:=xlDown
>> Range("G2").Select
>> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
>> Range("A1").Select
>>
>> End Sub
>>
>> It simply inserts a new row 2 and places a concatenation formula in cell
>> G2.
>>
>> How can this be optimized? I know SELECTS are undesireable but I'm more
>> interested in how to get the formula in A1 reference style.
>>
>> Thanks!
>>
>> Biff
>>
>>
>>

Thanks, Dana.

One of these days I'll get into VBA!

Biff

"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
>> interested in how to get the formula in A1 reference style.
>
> Would this idea work? HTH :>)
>
> Sub foo()
> Rows(2).Insert
> Range("G2").Formula = "=B2&C2&D2&E2&F2"
> Range("A1").Select
> End Sub
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi folks!
>>
>> I have this simple macro that was created using the recorder.
>>
>> Sub foo()
>>
>> Rows("2:2").Select
>> Selection.Insert Shift:=xlDown
>> Range("G2").Select
>> ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]"
>> Range("A1").Select
>>
>> End Sub
>>
>> It simply inserts a new row 2 and places a concatenation formula in cell
>> G2.
>>
>> How can this be optimized? I know SELECTS are undesireable but I'm more
>> interested in how to get the formula in A1 reference style.
>>
>> Thanks!
>>
>> Biff
>>
>>
>
>