1

I have an Excel 2013 generated macro.

In the first command, what is the equivalent of Ctrl End to specify the full range? The number of rows will change daily and so need a method to encompass all rows.

In the second command, how do I use a Date function to specify (today-1) for Criteria1 and (today) for Criteria2.

I tried Date, Date(), "Date" and other variations.

I am trying to automate a report that normally takes 30m manually every day. This is a small subset of the code.

Sub Macro7Step2()
'
' Macro7Step2 Macro
'

'
    ActiveSheet.Range("$A$1:$AT$9272").AutoFilter Field:=17, Criteria1:= _
        ">=8/23/2021", Operator:=xlAnd, Criteria2:="<=8/24/2021"
    ActiveSheet.Range("$A$1:$AT$9272").AutoFilter Field:=3, Criteria1:= _
        "In Production"
End Sub
Community
  • 1
  • 1
sunandwavs
  • 11
  • 3
  • 2
    Question 1: [Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Aug 24 '21 at 18:42
  • 2
    Question 2: Try `">=" & Date - 1` and `"<=" & Date`. – BigBen Aug 24 '21 at 18:53
  • 1
    @BigBen Thank you! Both of your responses worked! Exactly what I was looking for – sunandwavs Aug 25 '21 at 17:41
  • Okay, the Find code to identify the last row worked but not sure how to integrate that output to the Range object. There are many embedded blank rows so CurrentRegion doesn't seem to be working. The anchor cell is $A$1. Number of columns are usually fixed (may change once a year). Number of rows grows daily. – sunandwavs Aug 25 '21 at 21:19

1 Answers1

0

CurrentRegion and Filtering Dates

Option Explicit

Sub Macro7Step2()
'
' Macro7Step2 Macro
'

'
    Dim ws As Worksheet: Set ws = ActiveSheet
    ' or rather something like the following instead:
    'Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If

    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    
    rg.AutoFilter Field:=17, _
        Criteria1:=">=" & Date - 1, Operator:=xlAnd, Criteria2:="<=" & Date)
    ' If the previous doesn't work (it doesn't on my end),
    ' try the following instead (note the '\'):
    'rg.AutoFilter Field:=17, _
        Criteria1:=">=" & Format(Date - 1, "mm\/dd\/yyyy"), Operator:=xlAnd, _
        Criteria2:="<=" & Format(Date, "mm\/dd\/yyyy")
        
    rg.AutoFilter Field:=3, Criteria1:="In Production"
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thanks. The Date portion works! The range setting code is not working, but not getting an error either. Weird. How can I debug to see why? – sunandwavs Aug 25 '21 at 18:19
  • I set a watch on rg. The Value is blank after the Set rg statement. – sunandwavs Aug 25 '21 at 18:44
  • This is the revised code ``` Sub Macro7Step2() ' ' Macro7Step2 Macro ' ' Dim ws As Worksheet: Set ws = ActiveSheet If ws.AutoFilterMode Then ws.AutoFilterMode = False End If Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion rg.AutoFilter Field:=17, Criteria1:= _ ">=" & Date - 1, Operator:=xlAnd, Criteria2:="<=" & Date rg.AutoFilter Field:=3, Criteria1:= _ "In Production" End Sub ``` – sunandwavs Aug 25 '21 at 20:21