excelloopsdelete-rowno-datavba

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

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..

Solution

  • 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