If Column B is blankdelete entire row

[Carin]

I am using the following macro to delete "completely empty" rows. I also need to delete some rows if a cell in column B has no value. How would I change this macro?
' DeleteBlankRows


Dim r As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
ActiveSheet.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:

 

[brian.wethington]

change this:

If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then

to this:

If Cells(r, "B") = "" then

Hope this helps!

 

[lenze]

A simpler version might be

Sub DeleteBlanks()
Dim cl as Range
For each cl in Range("$B$2:$B" & Range("$B$65536").End(xlUp).Row)
If cl = "" Then cl.EntireRow.Delete
Next cl
End Sub

lenze

 

[Scott Huish]

lenze:
If you are using a loop to delete rows, you want to go backward through the range, not forward.

Carin:
Perhaps:

Sub DelBlanks()
With Range("B:B")
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="="
    Range("B2:B" & Rows.Count).EntireRow.Delete
    .AutoFilter
End With
End Sub

 

[Boller]

[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Put this before your loop (so the loop is smaller).

 

[Scott Huish]

Good point, but wouldn't that eliminate the need for the loop at all?

Sub test()
On Error Resume Next
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

although I don't think that would delete rows with formula blanks.

 

[Boller]

Good point, but wouldn't that eliminate the need for the loop at all?

Sub test()
On Error Resume Next
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

although I don't think that would delete rows with formula blanks.

I read the original post as wanting to delete rows with column B blanks in addition to deleting completely blank rows.

I am interpreting "has no value" as meaning a blank cell, but of course, this may not be what the OP means.

 

[Scott Huish]

I read the original post as wanting to delete rows with column B blanks in addition to deleting completely blank rows.

I did too at first, but I think this is also true:

If Column B is blank then delete Entire row
but if Column B is not blank then the entire row is not empty, so I'm not sure the rest matters.

 

[Boller]

I read the original post as wanting to delete rows with column B blanks in addition to deleting completely blank rows.

I did too at first, but I think this is also true:

If Column B is blank then delete Entire row
but if Column B is not blank then the entire row is not empty, so I'm not sure the rest matters.

Yes, you are quite right. I wasn't thinking very logically.

 

[QuietRiot]

Good point, but wouldn't that eliminate the need for the loop at all?

Sub test()
On Error Resume Next
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

although I don't think that would delete rows with formula blanks.

yah this is the one I always use. its faster/cleaner

i would dump your original and stick with this one