pick date automatically [SOLVED]

[Sukumar]

Hi,

In following code, I want to change two things,

Cells.Select
  Selection.AutoFilter
  With ActiveSheet.Range("$A$1:$F$800")
  .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, "12/05/2016")
  .AutoFilter Field:=3, Criteria1:="Approved"
  End With

First, I want to change Range to select last low of Col F.

Second, I want to change Criteria to have yesterday's date, i.e. Today()-1

Sukumar

 

[Chihiro]

Something like below. In VBA Date is equivalent of Today() in formula.

  With ActiveSheet.Range("A1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
  .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Date - 1
  .AutoFilter Field:=3, Criteria1:="Approved"
  End With

 

[Sukumar]

I thank your reply. I am new to this forum and I need suggestions from moderator as how to mark this thread as "answered".

 

[Khalid NGO]

I need suggestions from moderator as how to mark this thread as "answered".

Hi,

You can mention by comment (like you did)

Thread Marked as [solved]

Regards,