Delete Rows If Date Less Than Date Value

[Guest]

I have a worksheet with dates that are inserted ascending order in column A.
I would like to delete date rows in column A, if they are less than a define
date value ('09-26-2007'), next shift all of the rows up by one below the
deleted row.

Please help me with a simple script to complete this task.

Thanks,

 

[Andrew]

I have a worksheet with dates that are inserted ascending order in column A.
I would like to delete date rows in column A, if they are less than a define
date value ('09-26-2007'), next shift all of the rows up by one below the
deleted row.

Please help me with a simple script to complete this task.

Thanks,

Hi

Assuming that the dates are indeed already sorted, and that the dates
start in A1, this should do the job:


Sub RemoveDates()
Dim datFirstRequiredDate As Date
Dim rng As Range
Dim i As Integer

'Find out the first date to keep
datFirstRequiredDate = _
InputBox("Enter the first date that you want to keep")

'Start with A1
Set rng = Range("A1")
'Count the number of rows to remove

Do Until rng.Offset(i).Value >= datFirstRequiredDate
i = i + 1
Loop
'Remove from row 1 to the last non-required row
'NOTE: if you want to keep row 1 (eg if it contains headings
' you will need to alter the line to ... .Rows("2:" & i).....

ActiveSheet.Rows("1:" & i).EntireRow.Delete

End Sub