ExcelBanter - Optimize simple macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Optimize simple macro (https://www.excelbanter.com/excel-worksheet-functions/28808-optimize-simple-macro.html)

Biff

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

Don Guillett

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

--
Don Guillett
SalesAid Software

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

Biff

Thanks, Don.

Biff

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

--
Don Guillett
SalesAid Software

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

Peo Sjoblom

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

Don Guillett

glad it helped

--
Don Guillett
SalesAid Software

"Biff" wrote in message
...
Thanks, Don.

Biff

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

--
Don Guillett
SalesAid Software

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

Dana DeLouis

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

Biff

Thanks, Peo.

Biff

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

Biff

Thanks, Dana.

One of these days I'll get into VBA!

Biff

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

All times are GMT +1. The time now is 03:38 PM.