Forum Discussion

Copper Contributor

Aug 13, 2021

Solved

Help with Macros?

I'm a newbie when it comes to using macros within an Excel spreadsheet - and am looking for a little help. I have used the "generic" Macro recording tool to create a "filtered" view of a table. ...

Macros and VBA

SpaceNorman
Aug 13, 2021
HansVogelaar That's worked perfectly! Thanks you very much!!!! May I ask a followup question?

My "Transaction Log" table is dynamic (i.e., rows are constantly being added as new expense records are added to the table. Meanwhile - the Range statements contained in the Macros are "explicit". I just noticed that when rows are added to the table - the Macros do not update. How do I modify the range definition to accommodate an constantly expanding range?

HansVogelaar

MVP

Aug 13, 2021

SpaceNorman

Try this:

Sub Macro1()
    Dim MyYear As String
    MyYear = Worksheets("New Summary").Range("D3").Value
    With Worksheets("Transaction Log").Range("A6:S271")
        .AutoFilter Field:=6, Criteria1:="Medical"
        .AutoFilter Field:=9, Criteria1:=MyYear
        .AutoFilter Field:=7, Criteria1:="Patrick"
    End With
End Sub

SpaceNorman

Copper Contributor

Aug 13, 2021

HansVogelaar That's worked perfectly! Thanks you very much!!!! May I ask a followup question?

My "Transaction Log" table is dynamic (i.e., rows are constantly being added as new expense records are added to the table. Meanwhile - the Range statements contained in the Macros are "explicit". I just noticed that when rows are added to the table - the Macros do not update. How do I modify the range definition to accommodate an constantly expanding range?