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?

 

[jj511]

I'm hoping you can help me. I need to do the same thing here--look for blank cells and the delete the row if there are blanks--BUT, I need to do this in a WORD table. Do you happen to have any code suggestions for this? Thanks in advance!

 

[zone709]

i need to use something like this, but my issue is if Column E,F,G & I,J,K, & M,N,O are empty. I need it to delete those rows. I need a range remember only way they delete the row if all those columns are empty. Let me know if oyu can help thank you.

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

 

[user2456]

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

Hello, I'm a begginer in VBA, I hope you see this. I don't really understand what your subs three and four do. I have files which I need to clean up in this way with thousands or up to a couple ten thousand rows. How can I modify these subs so that the entire row is deleted if the cell in a column is blank?

 

[Afro_Cookie]

Try

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

Hi Vog,

When I try this code I get a run time error '1004' with a message that says 'No cells were found.'

I also tried the modified version you posted using 'Activecell.Column' instead of "F" and got the same error. I'm using 365 and cannot figure out why it can't find cells in my table that are blank,

Any suggestions?

 

[MARK858]

When I try this code I get a run time error '1004' with a message that says 'No cells were found.'

It means your cells aren't truly Blank/Empty. Formulas returning "" and non-printing characters from importing data would both cause cells to appear blank/empty when in fact they are not.

 

[Afro_Cookie]

It means your cells aren't truly Blank/Empty. Formulas returning "" and non-printing characters from importing data would both cause cells to appear blank/empty when in fact they are not.

that make sense.

Is there a way to delete rows from a table that appear blank? My table has no formula or conditional formatting in it.

 

[MARK858]

In an empty cell put the formula

=CODE(A2)

changing the A2 to a cell that appears blank.
What number does it return?

Btw I am assuming that you didn't include the

On Error Resume Next

line VoG had in the code or else you shouldn't have returned the error.