Deleting rows with empty cells which may not be empty

Mr80s

New Member
Joined
Sep 1, 2019
Messages
20
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)

LastnameFirstNameType
Date​
EmailNumber
SmithMikeStudent
Jun 19-20​
something@somewhere1233
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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
If you use the autofilter, to filter on blanks & then delete them, does that get rid of them all?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
791
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I was thinking
VBA Code:
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:
VBA Code:
    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

New Member
Joined
Sep 1, 2019
Messages
20
Hey Fluff,

Thanks for the idea!

This code works
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted, but you can rewrite the code like
VBA Code:
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,289
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top