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,011
Office Version
  1. 2019
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,141
Office Version
  1. 2013
Platform
  1. Windows
It would be easier if you were to enter a Date Like 10/1/2021
Not Oct 21
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,914
Office Version
  1. 2019
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,141
Office Version
  1. 2013
Platform
  1. Windows
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,914
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,141
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,011
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the replies

I would like to delete the entire row for all dates < than month / year date entered in the inputbox, except row 1

See sample data with dates in Col F

 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,202
Office Version
  1. 365
Platform
  1. Windows
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.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,914
Office Version
  1. 2019
Platform
  1. Windows
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:

Forum statistics

Threads
1,147,453
Messages
5,741,214
Members
423,649
Latest member
steel1968

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
Top