PDA

View Full Version : [SOLVED] Currentregion and autofilter Help



Feebles
06-10-2016, 10:41 PM
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


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

jolivanes
06-10-2016, 10:55 PM
Try this

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

Feebles
06-10-2016, 11:35 PM
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

jolivanes
06-11-2016, 12:08 AM
You can change the 1 in

Cells(Rows.Count, 1)
to any column number that you'll think will be the longest one.
Otherwise use

lr = Cells.Find(" * ", , , , xlByRows, xlPrevious).Row
to find the last used row and use as

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

Feebles
06-11-2016, 12:42 AM
Awesome to say the least
Thanks
Graham