Filter date based on todays date

[behedwin]

Hey

I use this code to filter data in a combobox. It works great!!

Sub FilterPersonellListBox_Larare()
    Dim ws As Worksheet
    Dim lr As Long
    Dim FiltRng As Range
    Dim R As Range
Personalform.ListBox_listapersonal.Clear    'clear listbox
Set ws = Sheets("REGISTER")
Personalform.ListBox_listapersonal.ColumnCount = 4
Personalform.ListBox_listapersonal.ColumnWidths = "1;70;70;55"


With ws
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set FiltRng = Sheets("REGISTER").Range("N3:N" & lr)
    
    FiltRng.AutoFilter Field:=14, Criteria1:="Lärare"

    
    On Error Resume Next
    For Each R In FiltRng.Offset(1).SpecialCells(xlCellTypeVisible).Cells
        With Personalform.ListBox_listapersonal
            .AddItem
            .List(.ListCount - 1, 0) = ws.Cells(R.Row, "AL")
            .List(.ListCount - 1, 1) = ws.Cells(R.Row, "B")
            .List(.ListCount - 1, 2) = ws.Cells(R.Row, "C")
            .List(.ListCount - 1, 3) = ws.Cells(R.Row, "R")
        End With
    Next R
    On Error GoTo 0

    FiltRng.AutoFilter
    
End With
End Sub

However...
Now i want to use this line

    FiltRng.AutoFilter Field:=14, Criteria1:="Lärare"

And filter a date compared to todays date.

The question is this:

In column X i have dates of when my staff contracts are ending.
I want to filter out all dates in column X that are between todays date and 3 months ahead.
How can i do this?

 

[MARK858]

Possibly...

FiltRng.AutoFilter Field:=14, Criteria1:= _
        ">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(DateAdd("m", 3, Date))

Although I don't see how Field:=14 works when FiltRng is a single column

 

[behedwin]

That worked directly for me!
Rare, so rare!!

Thank you very much!

 

[MARK858]

You're welcome but very strange it works as Field:=14 should make it error as it should only be Field:=1 when the range is a single column.

 

[Fluff]

You're welcome but very strange it works as Field:=14 should make it error as it should only be Field:=1 when the range is a single column.

If the sheet is already filtered on all data, then it will work.

 

[MARK858]

If the sheet is already filtered on all data, then it will work.

Yes, good point although there is nothing in the code to indicate that it is (and a strange way of going about it as the OP removes the filter later in the code), but then I can't see what the OP is doing with the sheet so as long as they are happy then all good

Thanks for the input.

 

[Fluff]

Yes, good point although there is nothing in the code to indicate that it is (and a strange way of going about it as the OP removes the filter later in the code), but then I can't see what the OP is doing with the sheet so as long as they are happy then all good

Thanks for the input.

I agree that it seems a bit strange, but as you said, if it's just the 1 column being filtered there should be an error.

 

[Marcelo Branco]

Yes, good point although there is nothing in the code to indicate that it is (and a strange way of going about it as the OP removes the filter later in the code), but then I can't see what the OP is doing with the sheet so as long as they are happy then all good

Thanks for the input.

Yes, it seems very strange that it worked since the dates are in column X (???)

From post 1

The question is this:

In column X i have dates of when my staff contracts are ending.
I want to filter out all dates in column X that are between todays date and 3 months ahead.
How can i do this?

But, as you've said ..."so as long as they are happy then all good"

M.