Hello,
Hopefully a pretty quick question. My simple formula in C:13 shows as #Ref! after I run the delete VBA function. This formula is simply the cell above +1 and will never change. How do I make sure to keep this formula after deleting rows. Lastly, is there away to make sure users cannot delete up to the current year? Sorry for the loaded questions. I am just learning this VBA stuff and there is a lot to it. Thanks!
Here is my VBA to delete rows and here is the data after i ran the delete row function:
Hopefully a pretty quick question. My simple formula in C:13 shows as #Ref! after I run the delete VBA function. This formula is simply the cell above +1 and will never change. How do I make sure to keep this formula after deleting rows. Lastly, is there away to make sure users cannot delete up to the current year? Sorry for the loaded questions. I am just learning this VBA stuff and there is a lot to it. Thanks!
Here is my VBA to delete rows and here is the data after i ran the delete row function:
VBA Code:
Sub DeleteRows()
'Get active sheet
Dim act As Worksheet
Set act = ThisWorkbook.ActiveSheet
'Delete Rows from current bot row both tables
top_row = act.Range("AZ1")
bot_row = act.Range("AZ4")
Range("A" & bot_row).Offset(-1, 0).EntireRow.Delete
Range("A" & top_row).Offset(-1, 0).EntireRow.Delete
End Sub
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:O15 | D4 | =IFERROR(LET(f,SUM(FILTER(MASTER!$G3:$G10000,(MONTH(MASTER!$E3:$E10000)=D$2)*(YEAR(MASTER!$E3:$E10000)=$C4),"")),IF(f="","",f)),"") |
P4:P16 | P4 | =IF(SUM(D4:O4)=0,"",SUM(D4:O4)) |
Q4:Q16 | Q4 | =C4 |
C5:C15 | C5 | =C4+1 |
C16 | C16 | =#REF!+1 |
D16:O16 | D16 | =IFERROR(LET(f,SUM(FILTER(MASTER!$G14:$G10011,(MONTH(MASTER!$E14:$E10011)=D$2)*(YEAR(MASTER!$E14:$E10011)=$C16),"")),IF(f="","",f)),"") |