VBA/Macro to delete 1 row above referenced row

[keef2]

Hello All,

I have a relatively simple task but my attempts have not been sucsessful. Can anyone please help with the following:

I have a worksheet that I basically want to delete 1 row above the referenced rows. So "AZ1"& "AZ4" are the 2 cells that i use as a dummy to identify the last row of particular tables.

Essentially what I would like to do is if the user clicks the button delete row it will delete 1 row above AZ1 & AZ4. I did write a code to insert rows by referencing these 2 cells here but cannot figure out best way to delete 1 row above these references. Any help is much appreciated as always:

 

[Oaktree]

Use .offset(-1,0) and remember to delete rows from the bottom to the top.

bot_row = act.Range("AZ1")
top_row = act.Range("AZ4")
range("A"&bot_row).offset(-1,0).entirerow.delete
range("A"&top_row).offset(-1,0).entirerow.delete

or similar

 

[keef2]

Use .offset(-1,0) and remember to delete rows from the bottom to the top.

bot_row = act.Range("AZ1")
top_row = act.Range("AZ4")
range("A"&bot_row).offset(-1,0).entirerow.delete
range("A"&top_row).offset(-1,0).entirerow.delete

or similar

Thank you however, i may not have described the best. AZ1 and AZ4 are both my dummy cells that reference the current bottom of row for my data sets (in this case Row 16 is AZ1 and Row 41 is AZ4 but they change depending on how many rows the user inserts. I was hoping to click a "delete bot row button" to delete the row above my helper rows which are in grey. Hope this makes more sense.

 

[Oaktree]

OK. If AZ1 and AZ4 were solely there to determine the rows to delete, you wouldn't need those cells. Instead, you can use .end(xldown) to determine the last row in a block of cells.

Below, I used .Find to determine the block of cells for the Sale Year Total row (currently C27, but that could move as rows are added), and I assumed the Monthly Sales always started in row 4.

Sub DeleteRows()
Dim lngLastMonthlyRow As Long
Dim lngLastSaleYearRow As Long
Dim res

lngLastMonthlyRow = Range("C4").End(xlDown).Row
lngLastSaleYearRow = Range("C:C").Find("Sale Year Total").End(xlDown).Offset(-1, 0).Row

res = MsgBox("Delete the rows for " & Cells(lngLastSaleYearRow, 3) & "?", vbYesNo)

If res = vbYes Then
Cells(lngLastSaleYearRow - 1, 1).EntireRow.Delete
Cells(lngLastMonthlyRow - 1, 1).EntireRow.Delete
End If

End Sub

I imagine you'd do something similar for adding rows via your other button...

 

[keef2]

Use .offset(-1,0) and remember to delete rows from the bottom to the top.

bot_row = act.Range("AZ1")
top_row = act.Range("AZ4")
range("A"&bot_row).offset(-1,0).entirerow.delete
range("A"&top_row).offset(-1,0).entirerow.delete

or similar

So I have this working based on this code. However, my simple formula in C:13 shows as #Ref! after i run the delete 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!

working Sales Table VBA - klott.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
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	#REF!														#REF!
SALES SUMMARY

Cell Formulas
Range	Formula
C12	C12	=C11+1
D12:O12	D12	=IFERROR(LET(f,SUM(FILTER(MASTER!$G11:$G10008,(MONTH(MASTER!$E11:$E10008)=D$2)*(YEAR(MASTER!$E11:$E10008)=$C12),"")),IF(f="","",f)),"")
P12:P13	P12	=IF(SUM(D12:O12)=0,"",SUM(D12:O12))
Q12:Q13	Q12	=C12
C13	C13	=#REF!+1
D13:O13	D13	=IFERROR(LET(f,SUM(FILTER(MASTER!$G14:$G10011,(MONTH(MASTER!$E14:$E10011)=D$2)*(YEAR(MASTER!$E14:$E10011)=$C13),"")),IF(f="","",f)),"")

 

[Oaktree]

For C13's formula, instead of:

=C12

use

=INDIRECT("C"&ROW()-1)

By using INDIRECT, it won't matter if the row above it is deleted, as it will always reference the row immediately above it.

As far as checking if they're deleting up to the current year, you could use something like:

lngLastMonthlyRow = Range("C4").End(xlDown).Row
If cells(lngLastMonthlyRow,"C") <= year(date) then
  msgbox "Can only delete rows for future years", vbokonly
End
End If

lngLastSaleYearRow = Range("C:C").Find("Sale Year Total").End(xlDown).Offset(-1, 0).Row

...(rest of code)

 

[keef2]

For C13's formula, instead of:

=C12

use

=INDIRECT("C"&ROW()-1)

By using INDIRECT, it won't matter if the row above it is deleted, as it will always reference the row immediately above it.

As far as checking if they're deleting up to the current year, you could use something like:

lngLastMonthlyRow = Range("C4").End(xlDown).Row
If cells(lngLastMonthlyRow,"C") <= year(date) then
  msgbox "Can only delete rows for future years", vbokonly
End
End If

lngLastSaleYearRow = Range("C:C").Find("Sale Year Total").End(xlDown).Offset(-1, 0).Row

...(rest of code)

This is exactly what I was hoping for thank you so much!!!