Macro - delete row if column has certain date

JandeRegt

New Member
Joined
Feb 15, 2010
Messages
2
Hey All,

I am just a beginner at this and require some help.

I generate a report in excel of about 63000 rows (this differs) there are several columns with dates, I wish to remove all the rows with a date before and after certain dates.

It seems I would need a loop for that, but it is unclear to me how this works, so far I have this:

Sub TestMacro2()
Columns("D:E").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("L:M").Select
Selection.NumberFormat = "m/d/yyyy"
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Rows("1:1").Select
Selection.AutoFilter
Range("$D").Select
For Each cell In Selection
If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then
cell.EntireRow.Delete shift:=xlUp
End If
Range("$D").Select
Next cell
End
End Sub

can anyone please help me better understand this?
 

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.
Is the macro doing what you want it to do or not? If not, what doesn't it do that you would like it to do. The best way to understand what's happening is to open the VBA window and reduce it's size so you can see your spreadsheet, click your mouse in the VBA window at the start of the macro and then keep pressing F8. This scrolls through the macro one command at a time with the command highlighted so you can see where you are in the macro and see what it's doing on your sheet.

HTH

Regards

Paul
 
Upvote 0
There's a couple problems...

The biggest is this

If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then

In VBA, simply writing DATE refers to TODAY'S Date. Not the date that is in the cell you are referring to. If you want to use the date that is in a cell, refer to that cell.
Also, this
1 - 3 - 2009
is not the correct way to write a date in VBA. VBA is reading that like
1 MINUS 3 MINUS 2009.
Try #1/3/2009#

So that line should be written like
If cell.Value < #1/3/2009# Or Cell.Value > #2/3/2010# Then

there's another problem, but I'll let you digest this first, while I write out an explaination of the next problem..
 
Upvote 0
The next problem is this.
doing a For Each cell in Range... loop goes top to bottom.
So say the range is A1:A100, it's going to start in A1, then A2, then A3 etc...

So say it met the criteria on cell A4 for example.
A4 was between 1/3/09 and 2/3/10.
so row 4 get's deleted.
so far so good.

here's the problem.
Whatever WAS in A5, has now shifted to A4.
But the next loop moves on to test A5.
A5 now holds whatever WAS in A6.
So the value that was originally in A5 essencially get's skipped.

You have to write your code to go backwards.
A100 then A99, then A98 etc..

It's fairly simple but requires a very different method of looping.

check back in a minute and I'll post something..
 
Upvote 0
Try

Rich (BB code):
Sub test()
Range("D:E,L:M").NumberFormat = "m/d/yyyy"
Rows(2).Select
ActiveWindow.FreezePanes = True
Rows(1).AutoFilter
 
'Create a variable that = the last used row in column D
LR = Cells(Rows.Count, "D").End(xlUp).Row
 
'Create a looping variable # to go backwards from the last used Row# to 1
For i = LR To 1 Step -1
    If Cells(i, "D").Value < #1/3/2009# Or Cells(i, "D").Value > #2/3/2010# Then
        Rows(i).EntireRow.Delete shift:=xlUp
    End If
Next i
End Sub
 
Upvote 0
Ah it seems I have a lot to learn, as said I am completely new to this, I have some experience programming on graphic calculators in high school but that's about it ;)

I understood the shifting of rows after you delete one, zo I included the Delete shift:=xlUp statement (at least I thought that caused the macro to go back up one cell)

The thing about the Date & the statement, that was just stupid of me, I should have read those statements better, my appologies.

I just ran the macro you provided and it works splendidly except for my first row (which contains tekst) is also deleted, any statement I can include to prevent that?
 
Upvote 0
Probably you'd want to run the given code upto your second row (starting from last row)...

Like this:

For i = LR To 2 Step -1


Just replace 1 by 2 in the For statement
 
Upvote 0
Another way is to filter out all the rows you want to delete; and then delete them in one big hit. It tends to be considerably quicker than looping, particularly on big ranges.

See example 5 in the attached article - which demonstrated deleting rows using an autofilter with date range criterion. Article here
 
Upvote 0
Jon von der Heyden,
This is what I need...I have around 20,000 rows that I would like to delete the rows where the date is 90 days earlier than today's date. (if it's less than 90 days, then it's not deleted). When I click on your link to read the article, the page timesout and can't be loaded. Anything I can do to be able to read this article?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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