Delete 2 Rows above if specific cell is blank [Loop]

Question

I have a hard coded report that generates about 10 sections. After running the report some sections may not contain data. I want to search for those specific cells and delete the two rows (saection headers) above if that cell is blank (no data).

For example:

If cell B586 is blank, delete rows 584:585.

and then...

If cell B505 is blank, delete rows 503:504

etc..

There are many examples on google to accomplish this, do a search and try to write the code, if/when you encounter a problem, then ask a specific question about the problem you are having. Loop through column B, check each cell, if cell is empty, the use `Offset (-1).Resize(-2),EntireRow.Delete`. — GMalc, Jun 08 '18 at 15:16
I have about 30 pages of code for the program and report I'm stuck on one part which is extremely specific. Wasn't going to post it all here. Knew it was an easy solution and I appreciated the quick feedback. — MisterVBA11, Jun 08 '18 at 18:07

score 0 · Accepted Answer · answered Jun 08 '18 at 16:22

One way to achieve this would be as follows, the reason I delete the rows below (586) before checking the rows above (505) is because once you delete the row, the rows shift up, in essence changing the row numbers:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    If ws.Range("B586").Value = "" Then ws.Rows("584:585").Delete
    If ws.Range("B505").Value = "" Then ws.Rows("503:504").Delete
End Sub

Delete 2 Rows above if specific cell is blank [Loop]

1 Answers1