#REF! after VBA delete row

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
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:

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

working Sales Table VBA - klott.xlsm
CDEFGHIJKLMNOPQ
1Monthly Sales
3 January Febuary March April May June July August September October November December TOTAL SALES
42012$ 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,3562012
52013$ 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,7592013
62014$ 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,7302014
72015$ 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,0722015
82016$ 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,0582016
92017$ 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,3552017
102018$ 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,7492018
112019$ 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,5792019
122020$ 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,5392020
132021$ 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,2702021
142022$ 8,128,763$ 5,401,750$ 3,765,450$ 12,984,905$ 7,812,055$ 319,975$ 500,800$ 97,500 $ 39,011,1982022
152023 2023
16#REF! #REF!
SALES SUMMARY
Cell Formulas
RangeFormula
D4:O15D4=IFERROR(LET(f,SUM(FILTER(MASTER!$G3:$G10000,(MONTH(MASTER!$E3:$E10000)=D$2)*(YEAR(MASTER!$E3:$E10000)=$C4),"")),IF(f="","",f)),"")
P4:P16P4=IF(SUM(D4:O4)=0,"",SUM(D4:O4))
Q4:Q16Q4=C4
C5:C15C5=C4+1
C16C16=#REF!+1
D16:O16D16=IFERROR(LET(f,SUM(FILTER(MASTER!$G14:$G10011,(MONTH(MASTER!$E14:$E10011)=D$2)*(YEAR(MASTER!$E14:$E10011)=$C16),"")),IF(f="","",f)),"")
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
fixed with offset formula.... If there is a better way please let me know. Thanks again!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution
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):

Excel Formula:
=OFFSET(C15,-1,)+1
Book2
C
92018
102019
112020
122021
132022
142023
152024
Sheet1

Then it won't be affected by any row deletion, but the formula inside will keep referring to the cell above it.
 
Upvote 0
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):

Excel Formula:
=OFFSET(C15,-1,)+1
Book2
C
92018
102019
112020
122021
132022
142023
152024
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top