CODEWITHSUNDEEP
excelvba
Soren Christensen
Soren Christensen

Reputation: 11

VBA - Passing DateVariable to Autofilter fails

I'm brand new to VBA scripting, and has gotten stuck on a Date Variabel passing.

I'm trying to filter an Excel spreadsheet on a date field (named Dato). The filtering has to grab the last 90 days and order them by date Descending.

I've created a function, that identifies the date from where the filtering is to be applied. I can verify the date by a Message box. The date is stored in a variable (myOldDate), which I try to pass to an AutoFilter, which fails, as all data is removed from the view.

If I try manually to add the same date as found by the function, data is filtered perfectly.

I've been trying to use the Format function (newDate= Format(myOldDate, "dd-mm-yyyy") - but still no luck.

Anyone with a helping hand here???

Sub DateFilter()
    Dim lo As ListObject
    Dim iCol As Integer
    Dim lastrow As Long, i As Long
    Dim myOldDate As Variant
    Set lo = Ark1.ListObjects(1) 'Set reference to the first Table on the sheet
    iCol = lo.ListColumns("Dato").Index 'Set filter field
    lo.AutoFilter.ShowAllData 'Clear Filters
    lastrow = Cells(Rows.Count, iCol).End(xlUp).Row 'FIND LASTROW OF COLUMN STATED ABOVE
    myOldDate = (CDate(Application.WorksheetFunction.Max(Columns(iCol))) - 90) 'Finds date 90 before last date
    With lo.Range 
     .AutoFilter Field:=iCol, Criteria1:=">=myOldDate"
    End With
End Sub

Upvotes: 1

Views: 38

Answers (1)

urdearboy
urdearboy

Reputation: 14580

You need to concatenate your filter with your variable. Right now you are filtering for the literal string of >=myOldDate rather your variable 

Criteria1:= ">=" & myOldDate

Upvotes: 1

Related Questions