Better way to apply formula to multiple cells?

[bntringa]

Hi all,

First post, long time reader...

I've pasted some code below that does the following steps:
1.) Specifies a formula to combine two cells values into one
2.) Copy that formula and apply it to remaining records
3.) Convert the formula to a value (so that I can delete the source
rows)

This code works - but is there a sexier way to code this?

Thanks everyone!

- Brian



'Combine two column values for first record
Range("E4").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-2]) & "" "" & RC[-1]"

'Copy formula and paste to additional records
Range("E4").Select
Selection.Copy
Range("E5:E17").Select
ActiveSheet.Paste

'Copy and paste special formulas as values
Range("E4:E17").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Bob Phillips]

Depneds what you maen by sexy <G>

With Range("E5:E17")
.FormulaR1C1 = "=TRIM(RC[-2])&"" ""&RC[-1]"
.Value = .Value
End With


HTH

Bob

 

[Bob Phillips]

pity my spelling isn't <vbg>