If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Help with VBA Excel Script

I am trying to write a VB Script to do a few things in Excel. I am trying to filter a table based on certain criteria. It is working if I directly specify the table range (where the data is). I would like to make this more dynamic so that if the size of the table grows then the script will still work. How can set the table range to go to the last cell in the table? Please see code comments.

Code:

If Target.Range.Address = "$B$5" Then
With Sheets("PAR Where is it")
MsgBox "Event from B5 Fired"
'specific range from A2 to L110. What if table grows? How to find last cell in table?
ActiveSheet.Range("A2:L110").AutoFilter Field:=12, Criteria1:="<=5"
ActiveSheet.Range("A2:L110").AutoFilter Field:=2, Criteria1:="MTABC"
End With
End If

Re: Help with VBA Excel Script

ActiveSheet.UsedRange gives you just what the name suggests. However, that may not be exactly what you want, since you seem to be wanting to exclude the first row. A range comprising the used range except for the first row can be obtained like this: