If certain columns are empty within a range, delete entire row (Excel macro)

Question

I've been trying to work through this problem with VBA macros, but I'm struggling.

My data set looks like this:

    A    B    C    D    E    F    G    H
21  X
22       X
23            X
24       X    X
25                 X    X
26  X    X    X    X

I want a macro which, given a range, will check certain columns for blank cells, and delete the entire row.

For example, in the data set above...

• Range A21:H26
• Check specified columns (let's say A, B and C)
• If any of them are empty, delete the entire row

This should mean only row 26 wouldn't be deleted as it contains values in all of the specified columns.

My attempts

I had tried code like this, but it seems to delete rows with no real rhyme or reason that I can fathom:

Sub DeleteEmptyRowsInRange()
  
    myarray = Range("A21:H30")
  
    For r = 1 To UBound(myarray)
    
        For c = 1 To UBound(myarray, 2)
    
            If c = 4 Or c = 6 Or c = 13 Then
                If Trim(Cells(r + 1, c)) = "" Then rows(r).EntireRow.Delete
            End If
    
        Next c
    
    Next r
  
End Sub

Thanks in advance

Answer 1

After deleting rows, the cell range shifts upward. Therefore, the recommended approach is to either delete rows in reverse order or collect all unwanted ranges using the Union function.

Sub DeleteEmptyRowsInRange()
    Dim myArray, r As Long, c, delRng As Range
    myArray = Range("A21:H26").Value
    For r = 1 To UBound(myArray)
        For Each c In Array(1, 2, 3) ' Col A, B, C
            If Len(myArray(r, c)) = 0 Then
                If delRng Is Nothing Then
                    Set delRng = Cells(20 + r, 1)
                Else
                    Set delRng = Application.Union(delRng, Cells(20 + r, 1))
                End If
            End If
        Next c
    Next r
    If Not delRng Is Nothing Then
        delRng.EntireRow.Delete
    End If
End Sub