Re: Date Elimination

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Sandy Mann wrote:

Of course it is possible to delete the rows with the code that you posted. Try this code:

Option Explicit
Sub DeleteDate2()
Dim LastRow As Long
Dim dt As Date
Dim cell As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
dt = Date - 730
For Each cell In Range(Cells(1, 1), Cells(LastRow, 1))
cell.Value = _
Trim(Application.Substitute(cell.Value, _
Chr(160), ""))
If IsDate(cell.Value) Then
cell.NumberFormat = "d mmm yyyy"
If cell.Value < dt Then cell.ClearContents
End If
Next cell

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

On Error Resume Next
Range(Cells(1, 1), Cells(LastRow, 1)) _
.SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
End Sub

This does not get rid of the empty cells + lines, just the requested dates in excess of 730.
.

Relevant Pages