Excel vbs code for dynamic range referencing

[DenniBrink]

Greetings everyone! The following VBS script has fixed defined ranges. I need the ranges to dynamically change as new rows are added to the worksheets.

Sub SATFILTER()
' SATFILTER Macro
' Filters Master Daily Schedule Worksheet for Employees Assigned to Work Saturday.


Dim SATFILTER$
Dim Wksheet1 As Worksheet, Wksheet2 As Worksheet
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
Set Wksheet1 = Sheets("SAT_REVIEW")
Set Wksheet2 = Sheets("MASTER DAILY SCHED")
Set Rng1 = Wksheet1.Range("B8:C38") - 'Need to change to dynamic'
Set Rng2 = Wksheet2.Range("$C$4:$I$56") - 'Need to change to dynamic'
Set Rng3 = Wksheet2.Range("B5:C56") - 'Need to change to dynamic'
Set Rng4 = Wksheet1.Range("B8")


Rng1.Clear
Rng2.AutoFilter Field:=1, Criteria1:="<>NS"
Rng3.Copy Rng4
Rng2.AutoFilter Field:=1 'I believe this turns off the ant trail enabled by AutoFilter'
Wksheet1.Activate
MsgBox "Filtering Done!"

End Sub

Worksheet2 table number of columns are fixed ("B:I") and never will change. The table will only grow in size by the addition of rows. Currently lastrow = Range("B56:I56").

How best to implement the capability of dynamic cell referencing and improve the VBS script is my objective. I welcome all Excel enthusiasts collaborative ideas.

 

[mumps]

Maybe:

Sub SATFILTER()
    ' SATFILTER Macro
    ' Filters Master Daily Schedule Worksheet for Employees Assigned to Work Saturday.
    Application.ScreenUpdating = False
    Dim SATFILTER$
    Dim Wksheet1 As Worksheet, Wksheet2 As Worksheet
    Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
    Set Wksheet1 = Sheets("SAT_REVIEW")
    Set Wksheet2 = Sheets("MASTER DAILY SCHED")
    Set Rng1 = Wksheet1.Range("B8:C" & Range("C" & Rows.Count).End(xlUp).Row) 'Need to change to dynamic'
    Set Rng2 = Wksheet2.Range("$C$4:$I$" & Range("I" & Rows.Count).End(xlUp).Row)  'Need to change to dynamic'
    Set Rng3 = Wksheet2.Range("B5:C" & Range("C" & Rows.Count).End(xlUp).Row) 'Need to change to dynamic'
    Set Rng4 = Wksheet1.Range("B8")
    
    
    Rng1.Clear
    Rng2.AutoFilter Field:=1, Criteria1:="<>NS"
    Rng3.Copy Rng4
    Rng2.AutoFilter Field:=1 'I believe this turns off the ant trail enabled by AutoFilter'
    Wksheet1.Activate
    MsgBox "Filtering Done!"
    Application.ScreenUpdating = True
End Sub