PDA

View Full Version : Solved: Delete rows by dates



Shazam
06-19-2006, 12:43 PM
Hi everyone!

I would like to delete the rows in column A depends what today?s date. In the example attachment below there are 3 worksheets tabs that it would give you a better idea what I?m looking for.

Jacob Hilderbrand
06-19-2006, 06:41 PM
Do you want to delete the values that are not today's date?


Option Explicit

Sub DelRows()

Dim i As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 6 Step -1
If Range("A" & i).Value <> Date Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub

Shazam
06-19-2006, 07:59 PM
Do you want to delete the values that are not today's date?


I'm sorry if I did not explain it well. In column A the dates are 3 days apart from each other. For instance today is 06/19/2006 so the code should delete everything except the 06/22/2006 dates that example is in worksheet tab "Example 5". I put more examples in the workbook below.

mdmackillop
06-20-2006, 12:48 AM
A tweak to Jake's code, asking for the kept date.
Option Explicit

Sub DelRows()
Dim i As Long
Dim LastRow As Long
Dim Dt As Long
Dt = DateValue(InputBox("Insert date to keep"))
Application.ScreenUpdating = False
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 6 Step -1
If Range("A" & i).Value <> Dt Then
Range("A" & i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

Shazam
06-20-2006, 06:08 AM
Hi again mdmackillop,


I like your method of the input box. But the user who's going to run the macro is not computer literate. If possible can the code could be done in just one click of a button? If not thats ok.

mdmackillop
06-20-2006, 10:33 AM
Hi Shazam,
You have 3 different dates shown. If you can tell how these are distinguished for each sheet then something could be done, or if a value was present say in A1 that could be used to "offset" from the current date, that could also work. Your workbook use is not clear, so I can't suggest the "best" way to do this.

Shazam
06-21-2006, 06:03 AM
Hi mdmackillop,



I talked to the user and he is ok with the input box method. Problem solved.


Thank You!

Shazam
06-22-2006, 07:11 AM
Hi mdmackillop,

When you have a chance I would like a little modification to your code. You helped me before on the Inputbox error handling here is a link below.

Look in column G in the sample workbook below the user only could input those dates and nonthing else in the inputbox.

http://www.vbaexpress.com/forum/showthread.php?t=8205






Sub DelRows()


Dim i As Long

Dim LastRow As Long
Dim Dt As Long
Dt = DateValue(InputBox("Insert date to keep"))
Application.ScreenUpdating = False
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 6 Step -1
If Range("A" & i).Value <> Dt Then
Range("A" & i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub