Delete rows with dates

[Bestmiler]

Hi guys,

I'm trying to write a macro that would allow me to delete a row if the cell in column is a date (for ex: 10/13/2016). so what I would have left are just dates. In the list now there are blank cells and cels with just spaces and obviously dates.

Unfortunately this column can big quite larger at times (50,000 +). I've tried using isdate which works but it takes a really long time.

Another idea was to use specialcells and use that but I don't know of any way for it select just dates (if it even exist).

 

[Hui]

Can you:

1. Please clarify your requirements
Your post above says
allow me to delete a row if the cell in column is a date (for ex: 10/13/2016). so what I would have left are just dates.

If you delete Dates, what you have left is everything except dates

I suspect you meant:
allow me to delete a row if the cell in column isn't a date (for ex: 10/13/2016). so what I would have left are just dates.

2. Can you post the file & the code you have ?

 

[Bestmiler]

That is what I meant. I want the macro to delete anything that is not a date (for now)
So far I have this which isn't correct but it's what I started with. I'm open to other suggestions as well. The reason I wrote it like this is because specialcells is quick. Although I know I can always use IsDate which takes a bit longer especially if my data has 50000+rows.

On Error Resume Next

Range("E2:E100000").Select

Selection.Specialcells(xlCellTypeConstants, xlDate).EntireRow.Delete

 

[Marc L]

Hi !

Hey Brooklyn boy, what did you not understand :

Can you post the file & the code you have ?

So « No arm, no chocolate ! »

Without any material I just can explain how to delete non contiguous rows …

Maybe the fastest way is not to delete non contiguous rows
but make them contiguous and instead of delete them just clear them !
And it's almost easy without any VBA code !

A helper column with 0 for rows to keep and 1 for those to clear.
Or instead of 0 / 1 it can be the couple FALSE / TRUE, no matter …

As a real date is just a number inside Excel,
as you wrote only dates, blank or space are in column,
it's easy to use a formula to fill this column, for example for row #2 :

=NOT(ISNUMBER(D1))​

Result is FALSE for a date and TRUE for blank or space cell …

Copy down formula.
Now to make rows contiguous just select entire data range
including helper column and sort this column in ascending order :
data range starts now with dates …
Select first row with TRUE until last row and just clear them !
Clear the remaining helper column, that all folks !

And like all Excel actions can be automatized within a VBA code …

Or you can use SpecialCells method in two instructions :
one for blank cells and second for text constants
as explained in VBA inner help.
Or at once using Union method for both ranges …

Or a mixed way using an helper column filled via a SpecialCells Offset !
​

 

[Bestmiler]

Thx for your help guys.

I was able to add a few lines of code so that any empty cells or any cells with texts can be deleted by adding this:

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete

So now I want to do the reverse which is keep all cells with dates. I'm not aware of any way to do the opposite of the above by adding something like NOT with SpecialCells.

Any help would appreciated?

 

[Marc L]

But both codelines keep yet all cells with dates !
And by logic can't be using NOT 'cause SpecialsCells returns an object !
Just use appropriate parameter or …

… easy - and could be faster - way is formula within a helper column.
​

 

[Marc L]

I forgot this :

I've tried using isdate which works but it takes a really long time.

It is the way how you delete rows using IsDate function which is long !

As IsDate function is one of the two safer way
if data are mixed with numbers and dates …
​

 

[jindon]

See if this is faster.
1) UDF

Function NonDate(r) As Boolean
    NonDate = Not IsDate(r)
End Function

2) find any available vertical consecutive 2 cells(e.g Z1:Z2)
3) Enter =NonDate(M2) in Z2 while Z1 left blank
4) AdvancedFilter and use Z1:Z2 for Criteria range.

This should filter all the row(s) that are not dates, and you can delete them.

Of course, you can write vba code for filtering/delete process, if it is faster.