Clear Data if date in Col F is equal to or less than a certain month and year

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have a macro to clear data below

I would like the macro amended to include an input box to enter the month and year so that if the dates in col F (format dd/mm/yyyy) are less than a certain month and year then the data must be cleared, otherwise left intact

If input box contains for Eg Oct 2021, then all dates in Col F prior to Oct 2021 to be cleared except row 1, which contains headers

Code:
 Sub Clear Data ()
Dim LR As Long
With Sheets("Data Import")
.UsedRange.ClearContents
  End With
 End Sub

Your assistance in this regard is most appreciated
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Do you want to delete the entire row for all dates < than month / year date entered in the inputbox?


It would be easier if you were to enter a Date Like 10/1/2021
Not Oct 21

Entering date with just Month & Year should not be an issue

VBA Code:
MsgBox DateValue("Apr 2021")
End Sub

this will return 01/04/2021

Dave
 
Upvote 0
Do you want to delete the entire row for all dates < than month / year date entered in the inputbox?




Entering date with just Month & Year should not be an issue

VBA Code:
MsgBox DateValue("Apr 2021")
End Sub

this will return 01/04/2021

Dave
When I tried this it did not work like you showed
 
Upvote 0
When I tried this it did not work like you showed

DateValue - a string date can include unambiguous dates that contain month names If DateValue cannot recognise the text as a date it will error
I have used function many times as shown with no issues but you don't say why it did not work? If you are getting an error then you could just hard code the first day of the month to any input variable containing the month & year which should resolve.



Dave
 
Upvote 0
DateValue - a string date can include unambiguous dates that contain month names If DateValue cannot recognise the text as a date it will error
I have used function many times as shown with no issues but you don't say why it did not work? If you are getting an error then you could just hard code the first day of the month to any input variable containing the month & year which should resolve.



Dave
Well not sure why but now it did work.
I guess I should go to bed.

But I would still think entering a proper date would be a better solution.
 
Upvote 0
Hello Howard,

Does this work for you?

VBA Code:
Option Compare Text
Sub Test()

        Dim DtVal As String: DtVal = InputBox("Please enter a date.")
        If DtVal = vbNullString Then Exit Sub
        
        With Sheet1.[A1].CurrentRegion
                .AutoFilter 6, "<" & Format(DtVal, "MMM/YYYY")
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

End Sub

Cheerio,
vcoolio.
 
Upvote 0
hi,
give this a try & see if does what you want

VBA Code:
Sub DeleteDates()
    Dim LastRow         As Long, r As Long
    Dim MonthDate       As Variant
    Dim wsDataImport    As Worksheet
   
    Const SearchColumn  As Variant = "F"
   
    On Error GoTo myerror
    Set wsDataImport = ThisWorkbook.Worksheets("Data Import")
   
    'show user valid inputs
    Prompt = "Enter Month And Year" & Chr(10) & "e.g." & Chr(10) & _
              Format(Date, "mm yyyy") & " or " & Format(Date, "mm/yyyy") & Chr(10) & _
              Format(Date, "mmm yyyy") & " or " & Format(Date, "mmmm yyyy")
    Do
        'move cursor to end
        SendKeys "{END}"
        MonthDate = InputBox(Prompt, "Enter Date", Format(Date, "mmm yyyy"))
        'cancel pressed
        If StrPtr(MonthDate) = 0 Then Exit Sub
    Loop Until IsDate(MonthDate)
   
    'convert string date to date
    'string date can include unambiguous dates that contain month names
    MonthDate = DateValue(MonthDate)
   
    With wsDataImport
        LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
        For r = LastRow To 2 Step -1
            If .Cells(r, SearchColumn).Value < MonthDate Then .Rows(r).Delete
        Next r
    End With
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top