PDA

View Full Version : [SOLVED] Macro to subtotal A2 until blank cell - cany anyone please help?



leemcder
09-30-2018, 05:57 AM
Hi, I am recording a Macro on a spreadsheet I use every month, I always use the formula =subtotal(9,A2:A7103) but the range can change from month to month so I want it to subtotal from cell A2 until it hits a blank cell. Can anyone tell me how to do that? many thanks

leemcder
09-30-2018, 07:42 AM
I figured it out myself, code below if anyone needs it. Where it says +3, it will put the formula 3 rows down from the last cell, you can change this. Where is says -3 it will end the range 3 cells above where the formula is. If you change the location from +3 then you will also need to change the range in -3 e.g changing it to +1 you will need to change the formula range to -1


Dim myRng As Range
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 3, "A").FormulaR1C1 _
= "=SUBTOTAL(9,R[-3]C:R2C)"
End With