#REF! after VBA delete row

[keef2]

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:

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

working Sales Table VBA - klott.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Monthly Sales
3		January	Febuary	March	April	May	June	July	August	September	October	November	December	TOTAL SALES
4	2012	$ 217,787	$ 714,700	$ 80,019	$ 7,879,552	$ 3,673,765	$ 522,802	$ 2,398,672	$ 1,251,678	$ 28,843	$ 17,573	$ 9,400	$ 847,565	$ 17,642,356	2012
5	2013	$ 3,093,415	$ 1,090,780	$ 1,623,840	$ 1,796,440	$ 1,717,774	$ 1,044,612	$ 1,825,158	$ 3,733,815	$ 637,388	$ 869,792	$ 2,346,670	$ 823,075	$ 20,602,759	2013
6	2014	$ 2,078,895	$ 2,626,000	$ 6,653,426	$ 36,380	$ 2,859,745	$ 831,390	$ 1,937,547	$ 3,483,615	$ 2,140,838	$ 647,872	$ 1,137,422	$ 67,600	$ 24,500,730	2014
7	2015	$ 2,533,585	$ 2,091,747	$ 4,494,199	$ 1,702,275	$ 1,005,674	$ 2,167,017	$ 7,680,620	$ 110,592	$ 4,006,328	$ 10,878,519	$ 95,300	$ 3,564,216	$ 40,330,072	2015
8	2016	$ 409,465	$ 1,832,825	$ 3,244,407	$ 3,904,514	$ 2,306,938	$ 5,429,274	$ 843,556	$ 8,298,244	$ 4,035,878	$ 1,192,207	$ 7,591,000	$ 92,750	$ 39,181,058	2016
9	2017	$ 3,465,280	$ 1,513,817	$ 6,584,535	$ 879,800	$ 1,218,100	$ 2,192,697	$ 298,450	$ 3,302,787	$ 2,806,055	$ 3,908,009	$ 458,937	$ 1,412,888	$ 28,041,355	2017
10	2018	$ 4,351,558	$ 270,195	$ 2,116,150	$ 5,489,089	$ 2,985,495	$ 2,456,650	$ 8,331,072	$ 2,442,865	$ 3,261,885	$ 367,150	$ 304,861	$ 3,049,779	$ 35,426,749	2018
11	2019	$ 113,838	$ 7,071,370	$ 1,380,520	$ 747,983	$ 5,226,733	$ 1,606,520	$ 7,148,845	$ 1,351,090	$ 792,577	$ 7,278,820	$ 4,818,686	$ 2,516,597	$ 40,053,579	2019
12	2020	$ 1,057,457	$ 5,948,478	$ 2,712,030	$ 1,014,750	$ 1,511,408	$ 1,182,030	$ 4,516,640	$ 3,857,360	$ 2,769,944	$ 1,685,050	$ 1,704,698	$ 2,772,694	$ 30,732,539	2020
13	2021	$ 4,090,350	$ 2,652,478	$ 9,143,516	$ 9,014,203	$ 6,060,405	$ 2,350	$ 8,571,295	$ 11,603,880	$ 535,470	$ 13,199,409	$ 4,606,389	$ 383,525	$ 69,863,270	2021
14	2022	$ 8,128,763	$ 5,401,750	$ 3,765,450	$ 12,984,905	$ 7,812,055	$ 319,975	$ 500,800	$ 97,500					$ 39,011,198	2022
15	2023														2023
16	#REF!														#REF!
SALES SUMMARY

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)),"")

 

[keef2]

fixed with offset formula.... If there is a better way please let me know. Thanks again!

 

[smozgur]

Please post the mentioned solution that will help future readers, and/or let others know the solution you applied to suggest if there is a better way.

Otherwise, please do not mark a post as a solution that doesn't contain a working answer.

 

[keef2]

Please post the mentioned solution that will help future readers, and/or let others know the solution you applied to suggest if there is a better way.

Otherwise, please do not mark a post as a solution that doesn't contain a working answer.

Sorry about that. Simply updated formula to.

=OFFSET(C14,,)+1

 

[smozgur]

Thanks for posting the solution. This way, it will be more helpful for future readers.

By the way, I am also interested in your solution. According to your solution, you are still referring to a cell above the formula cell, so C14 could be still deleted, right?

However, if you, instead, refer to the cell that contains the formula calculation +1 (itself) and still use the OFFSET function, then it won't be affected.
According to your solution. the +1 cell should be C15. So, if you put the following formula in C15 (note that I am referring to the same cell that holds the formula):

=OFFSET(C15,-1,)+1

Book2
	C
9	2018
10	2019
11	2020
12	2021
13	2022
14	2023
15	2024
Sheet1

Then it won't be affected by any row deletion, but the formula inside will keep referring to the cell above it.

 

[keef2]

Thanks for posting the solution. This way, it will be more helpful for future readers.

By the way, I am also interested in your solution. According to your solution, you are still referring to a cell above the formula cell, so C14 could be still deleted, right?

However, if you, instead, refer to the cell that contains the formula calculation +1 (itself) and still use the OFFSET function, then it won't be affected.
According to your solution. the +1 cell should be C15. So, if you put the following formula in C15 (note that I am referring to the same cell that holds the formula):

=OFFSET(C15,-1,)+1

Book2
	C
9	2018
10	2019
11	2020
12	2021
13	2022
14	2023
15	2024
Sheet1

Then it won't be affected by any row deletion, but the formula inside will keep referring to the cell above it.

Thank you sir! This is what i found out i needed to do this weekend. Much appreciated.