VBA delete row if cell is blank

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
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.

Code:
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
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
 
Last edited:

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Try

Code:
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

Banned user
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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! :)
Code:
Sub testdata()
With Range("F1:F" & 50000)
    .Cells = "=if(rand()<.67,row(),"""")"
    .Value = .Value
End With
End Sub
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
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! :)
Code:
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.

Code:
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

Banned user
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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

Board Regular
Joined
Jun 4, 2010
Messages
212
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

Banned user
Joined
Nov 4, 2010
Messages
2,075
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.

Code:
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

Board Regular
Joined
Jun 4, 2010
Messages
212
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,580
Messages
5,637,211
Members
416,961
Latest member
sigrid6940

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