Deleting Rows that contain future Dates

jsully777

Board Regular
Joined
Feb 13, 2005
Messages
121
Hi,

I am having troubles deleting rows that contain future dates. I want to keep rows that have today's date and previous dates but delete all rows that have tomorrow's date and beyond. When I use the code below, it deletes only a few rows at a time. I have to keep hitting the macro button to complete the deletion.

Anyone out there that could help?


Sub Delete()

'**************************************************************'
'Display Only Today's Dates and Previous Dates'
Sheets("R149").Select
iRow = 4
Do Until Sheets("R149").Cells(iRow, 6) = ""
If Sheets("R149").Cells(iRow, 6) > Date Then
Selection.EntireRow.Delete
'Sheets("R149").Rows(iRow).Delete 'Delete Future Dates
iRow = iRow + 1
Else
iRow = iRow + 1
End If
Loop

'**************************************************************'
End Sub


Any Thoughts?

Thx,
Jim
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
ASSUMPTION: Dates are in column 6 of the Activesheet :wink:

:-> VERSION 1
Public Sub DeleteTheFuture()
For Rw = Cells(65536, 6).End(xlUp).Row To 1 Step -1
With Cells(Rw, 6)
If IsDate(.Value) And .Value > Date Then .EntireRow.Delete
End With
Next Rw
End Sub



OOORRRR.... This version allows you to work on any sheet ...even if not active :p

:-> VERSION 2
Public Sub SheetToWorkOn()
Call DeleteTheFutureV3("R149")
End Sub

Private Sub DeleteTheFutureV3(ShName)
With Sheets(ShName)
For Rw = .Cells(65536, 6).End(xlUp).Row To 1 Step -1
With .Cells(Rw, 6)
If IsDate(.Value) And .Value > Date Then .EntireRow.Delete
End With
Next Rw
End With
End Sub
 

jsully777

Board Regular
Joined
Feb 13, 2005
Messages
121
Didn't work

Nimrod,

My appologies... I accidently hit the "report" button... on your reply. Very Very sorry. After reading van pookie's annoucements, I feel bad.

I tried your suggestion using a macro button and I stumped....

Here is my code:

Sub Reorg_Data()

'************************************************************'
With Sheets("R149")
For Rw = .Cells(65536, 6).End(xlUp).Row To 1 Step -1
With .Cells(Rw, 6)
If IsDate(.Value) And .Value > Date Then .EntireRow.Delete
End With
Next Rw
End With


It gets stuck on "If IsDate(.Value) And .Value > Date "

BTW, greetings from the North Shore
Jim
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Try it this way then...

Public Sub DeleteTheFuture()
With Sheets("R149")
For Rw = .Cells(65536, 6).End(xlUp).Row To 1 Step -1
With .Cells(Rw, 6)
If IsDate(.Value) Then
If .Value > Date Then .EntireRow.Delete
End If
End With
Next Rw
End With
End Sub
 

jsully777

Board Regular
Joined
Feb 13, 2005
Messages
121
SSSWWWEEEETTTT

Thank you. It worked perfectly.

I'm new to Macros and VBA, so just want to say that your help is greatly appreciated....

Thx,
Jim
North Vancouver,
Mfg Eng.

:biggrin:
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Your very welcome ... it's always a pleasure to help those that appreciate the assistance. :wink:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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