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?

 

[NeelsBorstlap]

Try

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

If I want the column to be used to check for the blank cells and then delete the empty rows is the column that I am standing on at that moment how will the code look like?

 

[VoG]

You need to include the correct column letter in the code:

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

 

[NeelsBorstlap]

Thanks VoG

But, if I do not now the column letter that I want the code must use (where code must look for blank rows). This code will always use column "F" (or the one I put in the code),
I want a code that will check the column (like in "activecells") where my cursor is in that moment and I do not want to edit the code and change the column letter ever time it differ from the F column.
Hope you understand and can help.

 

[VoG]

Try

Sub deleteBlankRows()
On Error Resume Next
Columns(ActiveCell.Column).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[NeelsBorstlap]

Thx

And if I want to do the same with deleting columns instead of rows must the code be like this?

Sub deleteBlankRows()On Error Resume NextRows(ActiveCell.Row).SpecialCells(xlCellTypeBlanks).EntireColumn.DeleteEnd Sub</pre>

 

[VoG]

That should work.

 

[chrono2483]

Try

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

This is a great one that works, but is there a way to skip the first row, and get it to run from A2 downward?

 

[VoG]

This is a great one that works, but is there a way to skip the first row, and get it to run from A2 downward?

Try

Sub deleteBlankRows()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("A2:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[snb_]

sub M_snb()
   usedrange.offset(1).columns(1).specialcells(4).entirerow.delete
end sub

 

[KPM171]

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

Thank you for posting your solution. This is a great little bit of code.