define last value in filter criteria

[danovkos]

Hi all,
pls. how can i define in code, that i want to select the last (without "blank", "Nonblank" - those are the last) from filter criteria? Because i dont know, what will be the last data, i want to define - use the last in filter combo.

i have this code:


Sub SAP_Kries12_old()
Sheets("aktual").Select
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Selection.AutoFilter Field:=8, Criteria1:="<3", Operator:=xlAnd
End Sub

thank you

[lucas]

Maybe you can use SpecialCells(xlCellTypeVisible) and find the last row of that data:


Option Explicit
Sub a()
Dim TestRange As Range
Dim FinalRow As Long
Set TestRange = Range("B2").CurrentRegion
' exclude the header and select only visible cells
Set TestRange = TestRange.Offset(1, 0).Resize(TestRange.Rows.Count - 1, _
TestRange.Columns.Count)
Set TestRange = TestRange.SpecialCells(xlCellTypeVisible)
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(FinalRow).Select
End Sub



see attachment for example

I didn't use your filter macro but you can filter it several ways and see how it selects the last row of whatever data is visible.....hope this helps.

[danovkos]

thx for your code
but maybe i am not good explain what i want..
i need to filter last value in combo of filter not the last row/value in table
in your file in column B is the last value in this column in table "C" but in combo of filter is it XX because there is it sorted ascending. And what i need is automatic select the last value. In this case is it "XX".
I am not sure if i am clear