VBA - Highlight/Delete row if Range is Empty

Question

I have a range of data, with CASE ID's in Column A, and Issues (1 through 10, or Columns B through K) in Columns B onwards.

Once certain issues are ruled out as 'normal', they would be removed from the Issues sheet based on their respective column. For ex: CASE ID #25, Issue 4 is ruled OK, then it would be deleted from Row 25, Column 5 (or Column E) but the CASE ID would remain.

The goal is that by doing this check after the fact, it may leave certain rows entirely blank, from Column B onwards (since the CASE ID would already be there.)

My code doesn't function successfully. Once run, it highlights several rows that are not entirely blank in the target range.

I'm trying to pinpoint rows in the range B2:P & lastrow where the entire row is blank, and then highlight these rows and subsequently delete them.

Code:

Public Sub EmptyRows()


lastrow = Sheets("Issues").Cells(Rows.Count, "A").End(xlUp).row
On Error Resume Next
Sheets("Issues").Activate
For Each rng In Range("B2:P" & lastrow).Columns
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Interior.ColorIndex = 11
    'rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next rng

Application.ScreenUpdating = True


End Sub

The purpose of first highlighting is to test the code works. If successful, they would be deleted entirely.

Answer 1

Once run, it highlights several rows that are not entirely blank in the target range.

This is because you are selecting all blanks, instead of only rows where the entire row is blank.

See the code below

Public Sub EmptyRows()

With Sheets("Issues")

    lastrow = .Cells(Rows.Count, "A").End(xlUp).row    

    Dim rng as Range
    For Each rng In .Range("B2:B" & lastrow)

          Dim blankCount as Integer
          blankCount = Application.WorksheetFunction.CountA(rng.Resize(1,.Range("B:P").Columns.Count)) 

          If blankCount = .Range("B" & lastRow & ":P" & lastRow).Columns.Count Then

              Dim store as Range
              If store Is Nothing Then Set store = rng Else: Set store = Union(rng, store)

          End If

    Next rng

End With

store.EntireRow.Interior.ColorIndex = 11
'store.EntireRow.Delete

End Sub

Gathering the ranges first and then modified them (changing color or deleting) will help to execute the code faster.

Answer 2

Your description says Columns B through K, but your code has B through P...

You can do it like this (adjust resize for actual columns involved):

Public Sub EmptyRows()
    Dim lastRow As Long, sht As Worksheet, c As Range, rngDel As Range

    Set sht = Sheets("Issues")

    For Each c In sht.Range(sht.Range("A2"), sht.Cells(Rows.Count, 1).End(xlUp)).Cells
        If Application.CountA(c.Offset(0, 1).Resize(1, 10)) = 0 Then

            'build range to delete
            If rngDel Is Nothing Then
                Set rngDel = c
            Else
                Set rngDel = Application.Union(rngDel, c)
            End If

        End If
    Next c

    'anything to flag/delete ?
    If Not rngDel Is Nothing Then
        rngDel.EntireRow.Interior.ColorIndex = 11
        'rngDel.EntireRow.Delete '<< uncomment after testing
    End If

End Sub

Answer 3

Here is another approach, using CountA

For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Dim rng As Range
    Set rng = Range("A" & cell.Row & ":" & "P" & cell.Row)

    If Application.WorksheetFunction.CountA(rng) = 1 Then
        rng.EntireRow.Interior.ColorIndex = 11
    End If
Next cell