Filter to bottom of data

**Jiptastic** · 03-27-2013, 01:12 PM

I usually use the macro recorder to get code like this:

ActiveSheet.Range("$A$1:$C$39").AutoFilter Field:=2, Criteria1:= _
        "Sandra Loya"

When I run the macro, the data will vary in length, so I must change the bottom cell reference. So far I have just changed it to be a really low cell so that it will always cover a larger range than my data.

ActiveSheet.Range("$A$1:$C$8000").AutoFilter Field:=2, Criteria1:= _
        "Sandra Loya"

But is there an easy way to have the macro automatically reset the range to the bottom of the current data? Something like this:

ActiveSheet.Range("A1.CurrentRegion").AutoFilter Field:=2, Criteria1:= _
        "Sandra Loya"

**HaHoBe** · 03-27-2013, 01:34 PM

Hi, Jiptastic,

mabye

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:= _
        "Sandra Loya"

or

With ActiveSheet
  .Range("$A$1:$C" & .Cells(Rows.Count, "C").End(xlUp).Row).AutoFilter Field:=2, _
      Criteria1:="Sandra Loya"
End With

Ciao,
Holger

**Leith Ross** · 03-27-2013, 01:39 PM

Hello Jiptastic,

You were close. The code would be as below...

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:= "Sandra Loya"

However, there a couple of points to remember. The current region must be contiguous. A blank row or column defines the end of the region. The region must include cell A1 and must have at least 2 columns in the region.