Code to delete row if value in Column B is null

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
Here's an easy one for most of you.

I'm trying to figure out VBA code to loop through the rows in a worksheet named Calc and then delete the entire row if the value in column b is null. I know the first inclination will be to use AutoFilter, and that would be fine, so long as the dates in Column A are not unsorted. They are currently in ascending chronological order.

Just to give a better description of the worksheet. Column A only contains dates. Column B only contains integers, but some dates in column A do not have a corresponding integer in column B. These are the rows I'd like to delete.

Thank You.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
L

Legacy 68403

Guest
try the following code

Code:
Range("B1").Select
Do While Not IsEmpty(ActiveCell)
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlup
ActiveCell.Offset(0, 1).Select
Loop
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello darin100K,
Unless you're just looking to learn about looping - (in which case by all means post back),
there is no need to loop down column B for this providing you'll have fewer than 8,192
blank cells in your column B data. This one line should do it all.
Code:
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Forum statistics

Threads
1,136,369
Messages
5,675,359
Members
419,565
Latest member
Phil57

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