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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
change this:

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

Hope this helps!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
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
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

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