Delete Rows not needed Macro

[zone709]

I need to delete 2 kind of rows Macro. If H or I have a 0 or 0.00 in it. Then delete that row but i also need to delete one row above it also.
On Sheet2 i have Example the clean look and i am realizing if i can delete Column H or I with a 0 or 0.00 in them. Then also delete the row above it. Only Above not below.
You can see the rows with zeros i don't need. Then mostly not all have the same name above row. Deleting both i think would clean my whole sheet. (above only)

Test Macro Work.xlsx
	A	B	C	D	E	F	G	H	I
1		AMD
2			Bill	7/1/2024	110	Fine Street	7/31/2024	7,500.00	7,500.00
3		Total AMD						7,500.00	7,500.00
4		American
5		Beac						0	0
6		American General
7			Bill	8/14/2024	34394	Fine Street	9/13/2024	5,197.91	5,197.91
8		Total American General						5,197.91	5,197.91
9		Landscaping LLC
10		Landscaping LLC						0	0
11		Corporation
12		Corporation						0	0
13		Fuel Corpration
14		Fuel Corpration						0	0
15		Arrow
16			Bill	7/1/2024	21049	Fine Street	7/31/2024	10,450.00	10,450.00
17			Bill	8/1/2024	21170	Fine Street	8/31/2024	10,450.00	10,450.00
Sheet1

Example Results

Test Macro Work.xlsx
	A	B	C	D	E	F	G	H	I
1		AMD
2			Bill	7/1/2024	110	Fine Street	7/31/2024	7,500.00	7,500.00
3		Total AMD						7,500.00	7,500.00
4		American General
5			Bill	8/14/2024	34394	Fine Street	9/13/2024	5,197.91	5,197.91
6		Total American General						5,197.91	5,197.91
7		Arrow
8			Bill	7/1/2024	21049	Fine Street	7/31/2024	10,450.00	10,450.00
9			Bill	8/1/2024	21170	Fine Street	8/31/2024	10,450.00	10,450.00
Sheet2

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long, r As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    For r = lr To 2 Step -1
        If Cells(r, "H").Value = 0 Or Cells(r, "I").Value = 0 Then
            Range(Cells(r, "H"), Cells(r - 1, "H")).EntireRow.Delete
         End If
    Next r
End Sub

 

[zone709]

Hi thanks for the reply. After running this it comes out like this. Only trying to delete the 0 or 0.00 if in h and i and only one line above.
I highlighted in example 2 in yellow what i am trying to do. Any help would great thanks.

Test Macro Work.xlsx
	A	B	C	D	E	F	G	H	I
1		AMD
2			Bill	7/1/2024	110	Fine Street	7/31/2024	7,500.00	7,500.00
3			Bill	8/14/2024	34394	Fine Street	9/13/2024	5,197.91	5,197.91
4			Bill	7/1/2024	21049	Fine Street	7/31/2024	10,450.00	10,450.00
5			Bill	8/1/2024	21170	Fine Street	8/31/2024	10,450.00	10,450.00
Sheet1

Example 2 only in yellow i want to delete 0 or 0.00 in h or i and one row above. Trying to save the vendor names in column B as you can see if only the yellow are deleted it will keep the vendor names in B.

Test Macro Work.xlsx
	A	B	C	D	E	F	G	H	I
1		AMD
2			Bill	7/1/2024	110	Fine Street	7/31/2024	7,500.00	7,500.00
3		Total AMD						7,500.00	7,500.00
4		American
5		Beac						0	0
6		American General
7			Bill	8/14/2024	34394	Fine Street	9/13/2024	5,197.91	5,197.91
8		Total American General						5,197.91	5,197.91
9		Landscaping LLC
10		Landscaping LLC						0	0
11		Corporation
12		Corporation						0	0
13		Fuel Corpration
14		Fuel Corpration						0	0
15		Arrow
16			Bill	7/1/2024	21049	Fine Street	7/31/2024	10,450.00	10,450.00
17			Bill	8/1/2024	21170	Fine Street	8/31/2024	10,450.00	10,450.00
Sheet1

 

[myall_blues]

Try this minor alteration to @Michael M 's code.

Sub MM1()
Dim lr As Long, r As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    For r = lr To 2 Step -1
        If (Cells(r, "H").Value = 0 Or Cells(r, "I").Value = 0) And _
            (Cells(r, "H").Value <> "" Or Cells(r, "I").Value <> "") Then
            Range(Cells(r, "H"), Cells(r - 1, "H")).EntireRow.Delete
         End If
    Next r
End Sub

 

[zone709]

hi thanks for reply. It keep breaking at this spot.

If (Cells(r, "H").Value = 0 Or Cells(r, "I").Value = 0) And _
            (Cells(r, "H").Value <> "" Or Cells(r, "I").Value <> "") Then

My cells have 0.00 i also tried changing them to 0 but same thing. Also the cells with 0 in it have formulas. I copied and paste that also with no formulas and it still breaks.

 

[Alex Blakenburg]

What does "breaks" mean ?
It works fine on the sample data you provided in Post #1 and Post #3.
Did you do anything to the data before you created the XL2BB ? If you try it on the data that the XL2BB is based on does it work ?

 

[Umma123]

Here's a VBA macro that will delete rows based on your criteria. It checks columns H and I for values of 0 or 0.00, deletes the row containing the zero, and then deletes the row directly above it. You can place this in your VBA editor for your sheet:

vba
Copy code
Sub DeleteRowsWithZeroInHandI()
Dim ws As Worksheet
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to your sheet name if needed

' Start from the bottom row and work upwards
For i = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row To 2 Step -1
If (ws.Cells(i, 8).Value = 0 Or ws.Cells(i, 9).Value = 0 Or _
ws.Cells(i, 8).Value = 0.00 Or ws.Cells(i, 9).Value = 0.00) Then
ws.Rows(i).Delete
ws.Rows(i - 1).Delete ' Delete the row above
End If
Next i
End Sub

 

[zone709]

What does "breaks" mean ?
It works fine on the sample data you provided in Post #1 and Post #3.
Did you do anything to the data before you created the XL2BB ? If you try it on the data that the XL2BB is based on does it work ?

Sorry i Figured out why it was Debugging. When i originally run the sheet before this. At the bottom in H and I i have #ref why it wasn't working. Anyway to ignore #ref. So it wont do this?

 

[Alex Blakenburg]

Thanks for letting us know. Glad you got it working. I think the solution should be either Post #4 or Post #2 whichever you ended up using

 

[RoryA]

Anyway to ignore #ref. So it wont do this?

What do you want to happen with #REF errors? Delete them as if they were 0 or leave them alone?