VBA with autofilter

[wisewood]

I have a spreadsheet which uses autofilter for me to quickly analyse data.

is there any way that i can have a VBA script that will filter column A by the last value in that column?

for example - if the last 6 rows have 1191 in column A, i want to filter and show only those values.

 

[Andrew Poulsom]

Like this?

Sub Test()
    Dim Crit As Long
    With Columns(1)
        Crit = .Cells(.Rows.Count, 1).End(xlUp).Value
        .AutoFilter Field:=1, Criteria1:=Crit
    End With
End Sub

 

[Richard Schollar]

Dim vLastCellValue
vLastCellValue = Cells(Rows.Count,"A").End(xlUp).Value
Range("A1").Autofilter Fields:=1,Criteria1:=vLastCellValue

 

[wisewood]

Excellent. Thanks Andrew.

Now, I've got it activating when i do Ctrl+Shift+T (for example), if i do the same key combination again, and that filter is already in place, i want it to turn the filter off, and take me to the bottom of the list :D Any idea?

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Crit As Long
    With Columns(1)
        If .Parent.AutoFilterMode = False Then
            Crit = .Cells(.Rows.Count, 1).End(xlUp).Value
            .AutoFilter Field:=1, Criteria1:=Crit
        Else
            .AutoFilter
            .Cells(.Rows.Count, 1).End(xlUp).Select
        End If
    End With
End Sub

 

[wisewood]

Awesome.

Now, i don't want the autofilter to start from row 1, i want it to start at row 9...

 

[Andrew Poulsom]

Like this?

Sub Test()
    Dim Crit As Long
    With Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If .Parent.AutoFilterMode = False Then
            Crit = .Cells(1, 1).End(xlDown).Value
            .AutoFilter Field:=1, Criteria1:=Crit
        Else
            .AutoFilter
            .Cells(1, 1).End(xlDown).Select
        End If
    End With
End Sub