CODEWITHSUNDEEP
vbaexcel
Neira
Neira

Reputation: 33

Excel VBA - Get column value after multiple filter

I have an Excel table (with 24 columns, 5000 rows). In VBA, I filter this table to find a row according to critera (currently I am trying with hard coded cretiria values to test the code) :

With Worksheets("test")
    .AutoFilterMode = False
    With .Range("A1:X1")
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="2024"
        .AutoFilter Field:=3, Criteria1:="T351"
        .AutoFilter Field:=4, Criteria1:="ABS5032A"
        .AutoFilter Field:=11, Criteria1:="7"
        .AutoFilter Field:=12, Criteria1:="8"
        .AutoFilter Field:=14, Criteria1:="1002"
        .AutoFilter Field:=15, Criteria1:="YES"
        .AutoFilter Field:=17, Criteria1:="NO"
        .AutoFilter Field:=18, Criteria1:="12"
    End With
End With

This works fine, in my Excel table i can see the right row visible and the others are hidden.

The problem starts here. I would like to be able to get the value of the last column (column "X") of the visible row. To do it i tried this (after my filter) :

Dim rsltRng As Range
Set rsltRng = Worksheets("test").AutoFilter.Range.SpecialCells(xlCellTypeVisible)

When I copy rslRng in another sheet, it works fine, the right row is copied in the sheet :

rsltRng.Copy Worksheets("sheet").Range("A1")

But I would like to avoid to use another sheet, and directly get the value of the column I want.

Any idea of how I could do this ?

Thank you for your help !

Neira

Upvotes: 0

Views: 3555

Answers (1)

Steve Cooke
Steve Cooke

Reputation: 26

So you want the value in Column X of the last visible row? Try this:

Dim rsltRng As Range
Dim result As String
Dim rowNum As Integer
Dim colNum As Integer

With Worksheets("test")
    .AutoFilterMode = False
    With .Range("A1:X1")
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="2024"
        .AutoFilter Field:=3, Criteria1:="T351"
        .AutoFilter Field:=4, Criteria1:="ABS5032A"
        .AutoFilter Field:=11, Criteria1:="7"
        .AutoFilter Field:=12, Criteria1:="8"
        .AutoFilter Field:=14, Criteria1:="1002"
        .AutoFilter Field:=15, Criteria1:="YES"
        .AutoFilter Field:=17, Criteria1:="NO"
        .AutoFilter Field:=18, Criteria1:="12"
    End With


    Set rsltRng = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)

    rowNum = .Range("B1").End(xlDown).Row
    colNum = 24 'column X

    result = .Cells(rowNum, colNum).Text

End With

Upvotes: 1

Related Questions