sooty8
07-06-2008, 03:54 AM
Hi All
Below is a shortened macro I have just recorded and it is totalling ranges and as there are 400 rows to cover the question is can it be shortened? - lots of "End With's" and the row total ranges are not always 2 rows apart.
Sub Macro2()
Sheets("Totals").Select
Range("M3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M15").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
End With
End With
End With
End With
End With
End With
End With
End Sub
Help Much Appreciated
Sooty8
Below is a shortened macro I have just recorded and it is totalling ranges and as there are 400 rows to cover the question is can it be shortened? - lots of "End With's" and the row total ranges are not always 2 rows apart.
Sub Macro2()
Sheets("Totals").Select
Range("M3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
Range("M15").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
With Selection.Interior
.Color = 10079487
End With
End With
End With
End With
End With
End With
End With
End Sub
Help Much Appreciated
Sooty8