VBA Excel Macro question on how to convert hard coded values to variables

Question

I'm new to VBA so appreciate all re-directions. I have the below (Excel generated) macro. And have two questions. Both are to how to remove hard coding and move to variables.

In the first command, how do I 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 daily.

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

I've tried using Date, Date(), "Date" and several other variations but to no avail.

The specific version of Excel is 2013. If there are better ways to do this, feel free to show me how. I suspect there are many more and better methods.

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

Cheers! --Paul

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

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
@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

VBasic2008 · Answer 1 · 2021-08-25T05:57:46.970

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

edited Aug 25 '21 at 05:57

answered Aug 25 '21 at 05:45

VBasic2008

28,653
5
16
24

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

VBA Excel Macro question on how to convert hard coded values to variables

1 Answers1

CurrentRegion and Filtering Dates

`CurrentRegion` and Filtering Dates