locked
Get the last column value filtered. RRS feed

  • Question


  • Hi all, I have the following problem:

    In an excel spreadsheet I have a table consists of 6 columns (a, b, c, d, e, f), in the column to realize filter, and I need to get the last value in column b after application of the filter, I hope you can help me, greetings.
    Thursday, October 2, 2014 9:42 PM

Answers

  • Re: filter value

    Probably several ways to screw this up.
    One would be for the user to hide some rows that intersect with the filter.

    '---
    Sub BestAtTheBottom()
      Dim FilterMsg As String
      Dim FilterRng As Range
      Dim FilterCol As Long
      Dim N As Long

      FilterCol = 2
      Set FilterRng = Range("A1:F30").Rows ' <<< Adjust range (must be exact)

      For N = FilterRng.Rows.Count To 1 Step -1
         If FilterRng.Rows(N).Hidden = False Then Exit For
      Next

      If N < 2 Then
        FilterMsg = "Nothing showing"
      ElseIf N => FilterRng.Rows.Count Then
        FilterMsg = "No filter"
      Else
        FilterMsg = FilterRng.Cells(N, FilterCol).Value
      End If
      MsgBox FilterMsg
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by aslf010990 Friday, October 3, 2014 3:46 PM
    • Edited by James Cone Sunday, October 30, 2016 8:23 PM
    Friday, October 3, 2014 1:32 AM
  • Say you already have applied a filter and now want to programmatically get the value of the last visible row in column B.

    If you run the following sub it should show a message box telling you the last visible row in Column B and its value

    Sub Main()
        
        Dim lastRow As Long
        lastRow = Range("B" & Rows.Count).End(xlUp).Row
    
        Dim i As Long
        For i = lastRow To 2 Step -1
            If Range("B" & i).EntireRow.Hidden = False Then
                Exit For
            End If
        Next
        
        MsgBox "The last visible row in column B is " & i & vbCrLf & _
                "and its value is: " & Range("B" & i).Value
        
    End Sub



    • Edited by Michal Krzych Friday, October 3, 2014 7:36 AM
    • Marked as answer by aslf010990 Friday, October 3, 2014 3:45 PM
    Friday, October 3, 2014 7:36 AM

All replies

  • Re: filter value

    Probably several ways to screw this up.
    One would be for the user to hide some rows that intersect with the filter.

    '---
    Sub BestAtTheBottom()
      Dim FilterMsg As String
      Dim FilterRng As Range
      Dim FilterCol As Long
      Dim N As Long

      FilterCol = 2
      Set FilterRng = Range("A1:F30").Rows ' <<< Adjust range (must be exact)

      For N = FilterRng.Rows.Count To 1 Step -1
         If FilterRng.Rows(N).Hidden = False Then Exit For
      Next

      If N < 2 Then
        FilterMsg = "Nothing showing"
      ElseIf N => FilterRng.Rows.Count Then
        FilterMsg = "No filter"
      Else
        FilterMsg = FilterRng.Cells(N, FilterCol).Value
      End If
      MsgBox FilterMsg
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by aslf010990 Friday, October 3, 2014 3:46 PM
    • Edited by James Cone Sunday, October 30, 2016 8:23 PM
    Friday, October 3, 2014 1:32 AM
  • Say you already have applied a filter and now want to programmatically get the value of the last visible row in column B.

    If you run the following sub it should show a message box telling you the last visible row in Column B and its value

    Sub Main()
        
        Dim lastRow As Long
        lastRow = Range("B" & Rows.Count).End(xlUp).Row
    
        Dim i As Long
        For i = lastRow To 2 Step -1
            If Range("B" & i).EntireRow.Hidden = False Then
                Exit For
            End If
        Next
        
        MsgBox "The last visible row in column B is " & i & vbCrLf & _
                "and its value is: " & Range("B" & i).Value
        
    End Sub



    • Edited by Michal Krzych Friday, October 3, 2014 7:36 AM
    • Marked as answer by aslf010990 Friday, October 3, 2014 3:45 PM
    Friday, October 3, 2014 7:36 AM
  • thanks, greetings
    Friday, October 3, 2014 3:46 PM