How to delete a row if certain cells are blank in VBA

Question

I am trying to delete an entire row if cells between a certain range are all blank.

For example, for the range B6-H10, if the values from B6-H6 are blank, then delete row 6. It is worth noting that Column A will be populated for the entirety of the range.

At present, the following error is being returned:

'Cannot use that command on overlapping sections'

I believe this error occurs as a result of a value being present within one of the cells within the given range.

The code at present can be found below:-

  Public Sub DeleteERows()

  Dim rng As Range

  Set rng = Range("B6:H10")

  rng.SpecialCells(xlCellTypeBlanks).EntireRow.Select
  rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

  End Sub

Any thoughts much appreciated.

https://stackoverflow.com/questions/54347053/delete-row-if-cell-is-blank — Siddharth Rout, Jun 16 '19 at 13:49
Please search Stackoverlow there are many more posts similar to yours — Siddharth Rout, Jun 16 '19 at 13:51
https://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows — Siddharth Rout, Jun 16 '19 at 13:52
Tyoe `delete blank cells stackoverflow` in google and see how many resutls you get. — Siddharth Rout, Jun 16 '19 at 13:53

score 2 · Answer 1 · answered Jun 16 '19 at 13:44

Multiple ways really, but let me start by assuring you, what you really want to do is avoid .Select.

Instead, try:

Option Explicit

Sub DelRows()

Dim X As Long
With ThisWorkbook.Sheets("Sheet1") '<-- Your sheetname goes here
    For X = 10 To 6 Step -1
        If Application.WorksheetFunction.CountA(.Range(.Cells(X, 2), .Cells(X, 8))) = 0 Then
            .Rows(X).EntireRow.Delete
        End If
    Next X
End With

End Sub

How to delete a row if certain cells are blank in VBA

1 Answers1