Forum Discussion
Help with Macros?
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?
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 SubHansVogelaar 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?
As Peter Bartholomew suggests, I'd use either a defined name or a table.
For the former, see:
All you need do is use defined Names to identify your data ranges and use the Name in place of the direct cell reference (the name is also placed within double quotes). Personally I never use a direct cell reference other than to define a Name, so I am biased.
If you are using Excel Tables and choose to invest more time into the coding then you could look up 'ListObject' and the 'ListObjects' collection of Tables. The columns are identifiable by name.
