Delete All Rows that Have Any Date in Column B

[Hobolord]

Hello,

I am using excel 2013 on Windows 7 on a PC.


I am trying to figure out how to delete an entire row if the value in the cell in Column B is a date.

I have the code to delete the row if the cell in column B is blank, but I can't figure out how to do this for a date.

Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Any ideas?

Thank you,

Hobo

 

[Hobolord]

I may have figured out a solution to my issue by using the following formula, and using autofilter to delete any rows with "Delete":

=IF(LEN(B1)=5,"DELETE","")

Can someone confirm that dates will always have a length of 5?

Or any other solutions?

Thanks again,

Hobo

 

[ismii]

mm/dd will always have a length of 5, but what about mm/dd/yy? That would be 8.
Excel could auto-format something that could give it a length of 5, or more if it's a date type.

You could loop through all the cells in that column and see if their "NumberFormat" matches the type of date you want to get rid of.
In the immediate window type "?Activecell.NumberFormat" and you'll see the numberformat for that cell. You can play with it like "Activecell.NumberFormat = "dd-mmm-yy" " and it will format a date into something like 13-Oct-16.
So if you check the cell and it has a numberformat of "mm-dd", then you could mark it for deletion, or delete it outright depending on what you want to do.

 

[Rick Rothstein]

I am trying to figure out how to delete an entire row if the value in the cell in Column B is a date.

What other kinds of values can be in Column B besides dates?

 

[Hobolord]

I tested out a bunch of different dates, and regardless of formatting, excel would return a Len() = 5.

The only other types of values in Column B are text strings that have Len() = 10. I think that my round-about way works. Thanks for replying!

 

[ismii]

Length of 5 maybe because excel sees it as a serial date (number of days since Jan 1900, which is 42k something). Interesting!

 

[Rick Rothstein]

I am trying to figure out how to delete an entire row if the value in the cell in Column B is a date.

I have the code to delete the row if the cell in column B is blank, but I can't figure out how to do this for a date.

Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I tested out a bunch of different dates, and regardless of formatting, excel would return a Len() = 5.

The only other types of values in Column B are text strings that have Len() = 10. I think that my round-about way works. Thanks for replying!

If your dates are real Excel dates (which that length of 5 would indicate they are) and if the only other values in the column are text strings (or blanks) and if these dates are constants (that is, they are not from formulas), then you can use this single line of code to delete them all at once...

Columns("B").SpecialCells(xlConstants, xlNumbers).EntireRow.Delete