MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to delete all rows with a date greater than a selected value?


Posted by Brad on December 19, 2000 6:46 AM

I need to delete all rows that have a date in column C in the form 12/19/2000 that are greater than a specified date. Any suggestions?

Brad


Posted by cpod on December 19, 2000 11:13 AM

Dim intCnt As Integer
intCnt = 2
Do Until Worksheets("sheet1").Cells(intCnt, 3) = 0
If Worksheets("sheet1").Cells(intCnt, 3) < Worksheets("sheet1").Range("RemovalDate") Then
Worksheets("sheet1").Cells(intCnt, 3).EntireRow.Delete
Else
intCnt = intCnt + 1
End If
Loop

The date for removal should be placed in a cell named "RemovalDate".


Posted by Brad on December 20, 2000 5:30 AM

cpod, I put that code in and nothing happened. It didn't change anything. Can anyone think of a different way to do this?

Brad

Posted by cpod on December 20, 2000 8:47 AM

You do have to activate the macro. what module did you put it in.

Posted by Brad on December 20, 2000 11:50 AM

cpod,
This is exactly how it looks in module 1 on sheet1 of Book3.

Sub dated()
Dim intCnt As Integer
intCnt = 2
Do Until Worksheets("sheet1").Cells(intCnt, 3) = 0
If Worksheets("sheet1").Cells(intCnt, 3) < Worksheets("sheet1").Range("RemovalDate") Then
Worksheets("sheet1").Cells(intCnt, 3).EntireRow.Delete
Else
intCnt = intCnt + 1
End If
Loop


End Sub

and yes I did execute the macro.
Brad

Posted by Celia on December 20, 2000 2:57 PM


Is it in the Module 1 code window or in the Sheet1 code window?
Have you created the name "RemovalDate"?
An alternative way to do what you want is to sort your data by column C and then select and delete the rows with dates greater than the relevant date.
Celia