PDA

View Full Version : Combining Code Part Deux



Djblois
04-20-2007, 07:05 AM
Thank everybody for all the help reducing my code. It is running much quicker and it is a lot smaller. Here are a few more things I would like to shrink:

First the same as in the last one: is it possible to combine these two lines where I copy a sheet over and name it at the same time?



reOrder.Copy After:=Sheets(1)
ActiveSheet.Name = "Original"


Next Cut and insert the range in one line:

Columns("A:A").Cut
Columns("F:F").Insert shift:=xlToRight

Also many places I do the same formating to multiple columns with a with statement:

With Range("L1", "N1")
.Value = "Date ______"
.Font.Bold = True
.Font.Size = 8
.WrapText = True
.Columns.AutoFit
End With

With Range("I1", "K1")
.FormulaR1C1 = "Date ______"
.Font.Bold = True
.Font.Size = 8
.WrapText = True
.Columns.AutoFit
End With

In that last one I know I can select it than run a sub that does the formating but I want to keep the with statement because of speed reasons.

mdmackillop
04-20-2007, 07:19 AM
With regard to Part 3; try using Styles.

Djblois
04-20-2007, 08:04 AM
What do you mean Styles?

Here are a few more:

Range("E" & i).FormulaR1C1 = "=(TODAY()-RC[1])/7"
Range("E" & i).Value = Range("E" & i).Value


Also:

Range("J" & i).Offset(-1, 0).FormulaR1C1 = "=RC[-3]-R[1]C[-3]"
If Abs(Range("J" & i).Offset(-1, 0)) < 0.01 Then Range("J" & i).Offset(-1, 0).ClearContents
Range("J" & i).Offset(-1, 0).Value = Range("J" & i).Offset(-1, 0).Value

mdmackillop
04-20-2007, 08:06 AM
What do you mean Styles?
Format/Style

mdmackillop
04-20-2007, 08:10 AM
Range("E" & i).FormulaR1C1 = "=(TODAY()-RC[1])/7"
Range("E" & i).Value = Range("E" & i).Value
or

Range("E" & i) = Format(Date - Range("D" & i) / 7, "dd/mm/yy")