Hi,
First time poster so apologies if code is not formatted / presented correctly (im quite new to VBA)
I'm trying to find (In Column K) a date found in dataset, starting 5 days ago from today, then 6 days, then 7 days and so on until a date is found, and then clear date from offset (-1,0)
However, below doesn't seem to come out of the loop
Any help is much appreciated!
Sub DeleteAgedData()
Dim DateToday As Date
Dim DateAged As String
Dim datecounter As Integer
datecounter = 5
DateToday = Date
DateAged = DateToday - datecounter
Dim oSht As Worksheet
Dim lastRow, i As Long
Dim strSearch As String
Dim aCell As Range
Set oSht = Sheets("Sheet 0")
lastRow = oSht.Range("K" & Rows.Count).End(xlUp).Row
strSearch = DateAged
Do While aCell Is Nothing
Set aCell = oSht.Range("K4:K" & lastRow).Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart)
datecounter = datecounter + 1
Loop
aCell.Offset(-1, 0).Select
ActiveCell.EntireRow.ClearContents
End Sub
First time poster so apologies if code is not formatted / presented correctly (im quite new to VBA)
I'm trying to find (In Column K) a date found in dataset, starting 5 days ago from today, then 6 days, then 7 days and so on until a date is found, and then clear date from offset (-1,0)
However, below doesn't seem to come out of the loop
Any help is much appreciated!
Sub DeleteAgedData()
Dim DateToday As Date
Dim DateAged As String
Dim datecounter As Integer
datecounter = 5
DateToday = Date
DateAged = DateToday - datecounter
Dim oSht As Worksheet
Dim lastRow, i As Long
Dim strSearch As String
Dim aCell As Range
Set oSht = Sheets("Sheet 0")
lastRow = oSht.Range("K" & Rows.Count).End(xlUp).Row
strSearch = DateAged
Do While aCell Is Nothing
Set aCell = oSht.Range("K4:K" & lastRow).Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart)
datecounter = datecounter + 1
Loop
aCell.Offset(-1, 0).Select
ActiveCell.EntireRow.ClearContents
End Sub