Solved: Macro:To Many End With's

[sooty8]

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

[Bob Phillips]

Sub Macro2()
Dim i As Long

With Sheets("Totals")
For i = 3 To 15 Step 2
With .Cells(i, "M")
.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
.Interior.Color = 10079487
End With
Next i
End With
End Sub

[Bob Phillips]

Or even loopless



Sub Macro2()

With Sheets("Totals")

With .Range("M3,M5,M7,M9,M11,M13,M15")

.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
.Interior.Color = 10079487
End With
End With
End Sub

[mdmackillop]

For irregular rows, you'll need to define each cell. You can use an array as shown, or get the data from another location in the workbook

Dim arr, a
arr = Array(3, 5, 7, 9, 11, 12, 15)

Sheets("Totals").Select
For Each a In arr
With Range("M" & a)
.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
.Interior.Color = 10079487
End With
Next

[mdmackillop]

Or even loopless
I must remember that term!

[sooty8]

Hi Xld

Many thanks isn't it easy when you know how,
This has to be the most efficient and helpful forum on the web with experts that outshine all others.

Thank you all

Sooty8