VBA delete row if cell is blank

[Sayth]

Just learning vba and trying to put it together.

If I want to delete a row if a value in its column is blank. So if it was column F and starting from F1.

F1 & F2 are blank and F8 & F9 are blank etc i would want to delete rows 1,2 & 8,9.

This is how I am setting it up but cannot get it to work.

Sub deleteBlankRows()
    Dim Cell As Range
    
    Cells("F1").Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    For Each Cell In Selection
        If Selection.SpecialCells(xlCellTypeBlanks) Is True Then
            Selection.EntireRow.Delete
        End If
    Next Cell
            
End Sub

I get a type mismatch saying I can't check if blank cells are true. Why?

 

[VoG]

Try

Sub deleteBlankRows()
On Error Resume Next
Columns("F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[JackDanIce]

Try:

Sub deleteBlankRows_1()

Dim rng1 as Range, rng2 as Range

Application.ScreenUpdating = False

Set rng1 = Range("F1", Range("F1").End(xlDown))
' Alternative to go from F1 to the last used cell in column F, you'd use:
' Set rng1 = Range("F1", Range("F" & Rows.Count).End(xlUp))

For Each Cell in rng1
  If rng2 Is Nothing then Set rng2 = Cell
  set rng2 = Union(rng2, Cell)
Next Cell

rng2.EntireRow.Delete

Set rng1 = Nothing: Set rng2 = Nothing

Application.ScreenUpdating = True

End Sub

 

[Sayth]

Try

Sub deleteBlankRows()
On Error Resume Next
Columns("F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Thanks worked a treat. I made it harder than it had to be.

 

[mirabeau]

Thanks worked a treat. I made it harder than it had to be.

Hi Sayth,

Here's a code generating some test data with random blanks and row numbers down to 50,000 rows.

For testing your blanks removal code. Just run it on a blank worksheet, then remove the blanks with your blanks removal code.

Try it out. See how well your code works.

Have fun!

Sub testdata()
With Range("F1:F" & 50000)
    .Cells = "=if(rand()<.67,row(),"""")"
    .Value = .Value
End With
End Sub

 

[Sayth]

Hi Sayth,

Here's a code generating some test data with random blanks and row numbers down to 50,000 rows.

For testing your blanks removal code. Just run it on a blank worksheet, then remove the blanks with your blanks removal code.

Try it out. See how well your code works.

Have fun!

Sub testdata()
With Range("F1:F" & 50000)
    .Cells = "=if(rand()<.67,row(),"""")"
    .Value = .Value
End With
End Sub

If I create code like this below then what it actually does is delete all the contents of column F. not only delete blank rows.

Sub testDelete()
'
' testDelete Macro
'
 
    With Range("F1:F" & 50000)
    .Cells = "=if(rand()<.67,row(),"""")"
    .Value = .Value
    End With
    
  
    Call deleteBlankRows
        
End Sub
Sub deleteBlankRows()
    On Error Resume Next
    Columns("F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[mirabeau]

If I create code like this below then what it actually does is delete all the contents of column F. not only delete blank rows.
...
...

Just illustrates one of the problems with that special cells approach.

They're fine to use if you don't have too many, but after a certain row size / number of blank cells they can get very slow, and can fail entirely without any error message.

For large problems there's better methods, but for the perhaps typical data Excel setup of less than a few thousand or so rows then speciallcellsblanks way is fine, and convenient.

 

[Sayth]

Just illustrates one of the problems with that special cells approach.

They're fine to use if you don't have too many, but after a certain row size / number of blank cells they can get very slow, and can fail entirely without any error message.

For large problems there's better methods, but for the perhaps typical data Excel setup of less than a few thousand or so rows then speciallcellsblanks way is fine, and convenient.

What would another good approach be? should I be testing if cell == ""

 

[mirabeau]

What would another good approach be? should I be testing if cell == ""

Well, depends I guess on why you're wanting it, also the size of your dataset.

Here's a few. The last two should easily handle datasets up to the full number of rows of an Excel 2007 worksheet. i.e. 1,048,576

The first two can also, but very much slower.

Specialcellsblanks is OK on small datasets, but gets slow on larger ones, and how do you know in advance when it will work and when it will wipe out all your data.

Use these codes one at a time of course.

Sub one()
Dim j As Long
For j = Cells(Rows.Count, "f").End(xlUp).Row To 1 Step -1
    If Cells(j, "f") = "" Then Cells(j, "f").Delete xlUp
Next j
End Sub
'******************************
Sub two()
Dim j As Long, c As Long
For j = 1 To Cells(Rows.Count, "f").End(xlUp).Row
    If Cells(j, "f") <> "" Then c = c + 1: Cells(j, "f").Cut Cells(c, "f")
Next j
End Sub
'*******************************
Sub testdata()
[a:j].ClearContents
With Range("F1:F" & 20)
    .Cells = "=if(rand()<.67,row(),"""")"
    .Value = .Value
End With
End Sub
'*******************************
Sub three()
Dim lr As Long, a, c As Long, j As Long
lr = Cells(Rows.Count, "f").End(xlUp).Row
With Cells(1, "f").Resize(lr)
    a = .Value
    .ClearContents
    For j = 1 To lr
        If Len(a(j, 1)) > 0 Then c = c + 1: a(c, 1) = a(j, 1)
    Next j
    .Resize(c) = a
End With
End Sub
'**************************************
Sub four()
Dim lr As Long
lr = Cells(Rows.Count, "f").End(xlUp).Row
Columns("g").Insert
With Columns("g").Resize(lr)
    .Cells = "=IF(RC[-1]="""","""",1)"
    .Value = .Value
    .Offset(, -1).Resize(, 2).Sort .Cells
    .Delete
End With
End Sub

 

[Sayth]

Thanks for taking the time to do this.

Using the first one not because its better but because I understand it. Modified it only to remove rows.

Sub one() 
    Dim j As Long For j = Cells(Rows.Count, "f").End(xlUp).Row To 1 Step -1
    If Cells(j, "f") = "" Then Cells(j, "f").EntireRow.Delete xlUp
    Next j 
End Sub