Currentregion and autofilter Help

I am trying to make data dynamic (as I add data daily) for autofilter
It works when i use the following code....but it is not dynamic

Code:

---

Private Sub CommandButton1_Click()
Crit = CLng(Range("G1").Value)
ActiveSheet.Range("$A$7:$N$112").AutoFilter Field:=1, Criteria1:=">=" & Crit, Operator:=xlAnd, Criteria2:="<=" & Crit

---

The data starts at A7 which is the header
I tried to change the ("$A$7:$N$112") to ("$A$7").currentregion.autofilter etc etc

any help here will be appreciated
Regards
Graham

Try this

Code:

---

ActiveSheet.Range("A7:N" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=">=" & Crit, Operator:=xlAnd, Criteria2:="<=" & Crit

---

Thanks Mark , it does pick up the bottom added rows, but only when I add a date to my criteria list, si I have to work that one out.
I will try on my own for a while to see if I can get grips on that
Thanks for your help
Graham

You can change the 1 in

Code:

---

Cells(Rows.Count, 1)

---

to any column number that you'll think will be the longest one.
Otherwise use

Code:

---

lr = Cells.Find(" * ", , , , xlByRows, xlPrevious).Row

---

to find the last used row and use as

Code:

---

ActiveSheet.Range("A7:N" & lr).AutoFilter Field:=1, Criteria1:=">=" & Crit, Operator:=xlAnd, Criteria2:="<=" & Crit

---

Awesome to say the least
Thanks
Graham