It's easier than most people think. Here's a couple of pointers that
I use heavily.
For single statements just delete the Select and Selection. fragments
and merge the rest of the 2 statements. For multiple statements
enclose everything in a 'With' clause and delete all references to
'Selection'. [While they do work in 99.9+% of the cases, there are a
few instances where the mechanical application of the rules doesn't
work. And, it is also possible to better leverage the XL object
model. For some examples see 'Beyond the macro recorder'
(
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm)]
OK, so let's apply these tranformations to your code. The first three
actions are:
Range("A5:G5").Select
Selection.Insert Shift:=xlDown
Range("B3:F3").Select
Selection.Copy
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
These can be replaced by using the first rule. Delete the
'Select...Selection.' part to get:
Range("A5:G5").Insert Shift:=xlDown
Range("B3:F3").Copy
Range("B5:F5").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Further down, the code does a bunch of things to cell B5:
Range("B5").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5>B6,TRUE)"
Selection.FormatConditions(1).Font.ColorIndex = 5
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5<B6,TRUE)"
Selection.FormatConditions(2).Font.ColorIndex = 3
Selection.Copy
The With clause rule applies here. The result is:
with Range("B5")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5>B6,TRUE)"
.FormatConditions(1).Font.ColorIndex = 5
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5<B6,TRUE)"
.FormatConditions(2).Font.ColorIndex = 3
.Copy
end with
And, that leaves one statement for you to transform:
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Andy
Thanks for that, have replaced as you have indicated, and it does
run more smoothly!
As I said, this macro was *recorded*, something even *I* can do!
Editing is another matter!
Thanks again.
George Gee
*Andy Brown* has posted this message:
I suppose it would help if I included the macro!
Range("A5:G5").Select
Selection.Insert Shift:=xlDown
Range("B3:F3").Select
Selection.Copy
For efficiency, you should take out as much physical selection as
poss. Forex, replace the last 2 lines with:
Range("B3:F3").Copy
Rgds,
Andy
