Filter by Date after

[Ladyj07]

Hi- I am not sure if this is possible. I looked online on using vba on filtering but none of the topics I read have anything similar of what I need. In the attached example sheet, to filter column D (Hire date) by Filter Date-->After and then I need to select the date that is in column F.

Once I get those results then the dates that are in the hire column (Column C) needs copy and paste in column F.

Capture.JPG

[sintek]

So...If I understand correctly...
If the dates in Col D are greater than a specific date then the dates in Col F must become those greater dates of Col D...

Sub J3v16()
Dim Dt As Date
Dt = CDate("20/03/2023")
With Cells(1).CurrentRegion.Columns(6)
    .Value = Evaluate("=If(Row(" & .Address & "),If(" & .Offset(, -2).Address & "> " & Dt & "," & .Offset(, -2).Address & "," & .Address & "))")
End With
End Sub

Depending on size of data...storing in array and then looping could be faster...

Sub J3v16()
Dim Data, Dt As Date, i As Long
Dt = CDate("20/03/2023")
With Cells(1).CurrentRegion
    Data = .Value2
    For i = 2 To UBound(Data)
        If Data(i, 4) > Dt Then Data(i, 6) = Data(i, 4)
    Next i
    .Value = Data
End With
End Sub

[Ladyj07]

@sintek Thank you! specially for the second script because my data is usually up to 20,00 records. Do you know if there is a way to get a box that pops so I can type the date? the date will change every month to something different. Sorry if this is too much to ask. Everything else worked as I wanted.

[sintek]

Replace...

Dt = CDate("20/03/2023")

With

Dt = Application.InputBox(FormatDateTime(Date, vbShortDate), , , 1)
If IsDate(Dt) Then
    Dt = DateValue(Dt)
Else
    MsgBox "INVALID"
End If

[Ladyj07]

@Sintek Thank you. You are the best!!

[sintek]

Glad to have contributed...Tx for rep +