If Column B is blankdelete entire row

Carin

Board Regular
Joined
Feb 4, 2006
Messages
224
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:
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
change this:

Code:
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
to this:
Code:
If Cells(r, "B") = "" then

Hope this helps!
 
Upvote 0
A simpler version might be
Code:
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
 
Upvote 0
lenze:
If you are using a loop to delete rows, you want to go backward through the range, not forward.

Carin:
Perhaps:

Code:
Sub DelBlanks()
With Range("B:B")
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="="
    Range("B2:B" & Rows.Count).EntireRow.Delete
    .AutoFilter
End With
End Sub
 
Upvote 0
Code:
[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Put this before your loop (so the loop is smaller).
 
Upvote 0
Good point, but wouldn't that eliminate the need for the loop at all?

Code:
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.
 
Upvote 0
Good point, but wouldn't that eliminate the need for the loop at all?

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Good point, but wouldn't that eliminate the need for the loop at all?

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

Forum statistics

Threads
1,222,241
Messages
6,164,787
Members
451,917
Latest member
WEB78

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
Back
Top