Deleting rows with empty cells which may not be empty

[Mr80s]

Hello All,

I'm trying to delete the entire row of a range if any cell in column "A" is empty, blank or appears to be blank. These are all supposed to be strings of last names but when I enter "=ISBLANK(A2) " in the cell the result is False, therefore it's not blank and .SpecialCells(xlCellTypeBlanks).EntireRow.Delete fails.

Is there a way to delete if the cell does not contain any text. aka last name?

I was thinking

With Worksheets("Sheet2").UsedRange
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
End With

But as mentioned, it's not deleting rows as cells are not blank. I'd like to know what's in the cell but that's just for my own education

Strangely the macro:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete shift:=xlUp
Does delete some of them but not all

As it left these...(below Mike Smith)

Lastname	FirstName	Type	Date​	Email	Number
Smith	Mike	Student	Jun 19-20​	something@somewhere	1233
		Student	Jul 29-20​
		Student	Jun 19-20​
		Student	Jul 29-20​

I also don't want to use "Select".

Does anyone have an idea to delete all rows in a range where "A" contains no last name but =ISBLANK(A2) is returning false?

Thanks!

 

[Fluff]

If you use the autofilter, to filter on blanks & then delete them, does that get rid of them all?

 

[rlv01]

I was thinking

With Worksheets("Sheet2").UsedRange
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
End With

If the reason is that the cells just appear to be blank, but actually have some " " space characters, then something like this should work:

    Dim R As Range
    With Worksheets("Sheet2").Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
        For Each R In .Cells
            If Trim(R.Value) = "" Then
                R.Value = ""
            End If
        Next R
        
        On Error Resume Next
        Set R = .SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

        If Not R Is Nothing Then
            R.EntireRow.Delete Shift:=xlUp
        End If
    End With

 

[Mr80s]

Hey Fluff,

Thanks for the idea!

This code works

Sub DeleteRows()
    With ActiveSheet
        .AutoFilterMode = False 'remove filter             
        With .Range("A:G")
            .AutoFilter Field:=1, Criteria1:="="
            On Error Resume Next ' for the case when there is no visible rows
            .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With            
        .AutoFilterMode = False 'remove filter
    End With
End Sub

Credit to Dmitry Pavliv for the code snippet.

Cheers!

 

[Fluff]

Glad you sorted, but you can rewrite the code like

Sub DeleteRows()
    With ActiveSheet
        .AutoFilterMode = False 'remove filter
        .Range("A:G").AutoFilter Field:=1, Criteria1:="="
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False 'remove filter
    End With
End Sub

Which removes the need for the on error.

 

[Mr80s]

Thank you!

 

[Fluff]

You're welcome