Getting the Range Address of an AutoFiltered Range

Anonymous
2011-06-10T16:16:30+00:00

I have a worksheet with the following column labels beginning in cell A3:

A3 = Year

B3 = Product ID

C3 = Jan Unpublished Estimate

D3 = Jan Published Estimate

E3 = Feb Unpublished Estimate

F3 = Feb Published Estimate

I have enabled filtering on the range A3:F200.  I then filter on Year (2011), and Product ID (203135).  This results in rows 6 through 97 being displayed, although the row numbers are not consecutive (i.e., not all the rows between 6 and 97 meet the filter criteria).

Is there a way to programmatically get the range address (specifically, the top and bottom rows) of the displayed range?

Thanks in advance for any assistance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-06-19T19:08:52+00:00

First off, I have no problem if you use Andreas's code instead of mine (he obviously has had more experience with filtering than I have and his code seems to be fully debugged)... it never bothers me when someone else's code is selected for use over the code I submit. Actually, I do this coding more for myself than for the person who asked the question... that they may be able to make use of it is a plus. I have always enjoyed doing puzzles and a good amount of questions that get asked in forums (and newsgroups) provide me with a ready supply of interesting puzzles to solve (this thread being one of them... which is one of the reasons I am doggedly attempting to "get it right").

As for the incorrect result you pointed out for the top filtered row... that was more a result of my not understanding what should get returned when the filter hid all of its filtered items. Initially, I figured the first visible row after the filter would be what was wanted. I guessed wrong on that.<g> Here are my functions fixed to account for the filter hiding all of its items...

Function GetFilteredRangeTopRow() As Long

  Dim HeaderRow As Long, LastFilterRow As Long

  On Error GoTo NoFilterOnSheet

  With ActiveSheet

    HeaderRow = .AutoFilter.Range(1).Row

    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row

    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)). _

                             SpecialCells(xlCellTypeVisible)(1).Row

    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0

  End With

NoFilterOnSheet:

End Function

Function GetFilteredRangeBottomRow() As Long

  Dim HeaderRow As Long, LastFilterRow As Long

  On Error GoTo NoFilterOnSheet

  With ActiveSheet

    HeaderRow = .AutoFilter.Range(1).Row

    LastFilterRow = .Range(Split(.AutoFilter.Range.Offset(1).Address, ":")(1)).Row

    GetFilteredRangeBottomRow = .Range(.Rows(HeaderRow + 1), .Rows(LastFilterRow + 1)). _

                                Find(What:="*", After:=.Rows(LastFilterRow + 1).Cells(1), _

                                SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    If GetFilteredRangeBottomRow = LastFilterRow + 1 Then GetFilteredRangeBottomRow = 0

  End With

NoFilterOnSheet:

End Function

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Andreas Killer 144.1K Reputation points Volunteer Moderator
2011-06-19T08:54:05+00:00

Please study this:

Option Explicit

Sub Main()

  Dim Data(1 To 10, 1 To 2)

  Dim i As Long

  Dim Msg As String

  Dim Bug As Integer

  For i = 1 To UBound(Data)

    Data(i, 1) = i Mod 2

    Data(i, 2) = 1

  Next

  For i = 1 To 5

    Cells.Clear

    With Range("C5")

      .Value = "Nr"

      .Offset(-2, 0) = "Something"

      .Offset(1, 0).Resize(UBound(Data), UBound(Data, 2)) = Data

      .Offset(UBound(Data) + 2, 0) = "Something"

      Select Case i

        Case 1

          .CurrentRegion.AutoFilter 1, "=0"

        Case 2

          .CurrentRegion.AutoFilter 1, "=1"

        Case 3

          .CurrentRegion.AutoFilter 2, "<>1"

        Case 4

          .CurrentRegion.AutoFilter 2, "=1"

        Case 5

          'No Filter

      End Select

    End With

    MsgBox AutoFilterTopRow & " - " & AutoFilterBottomRow

  Next

End Sub

Function AutoFilterTopRow() As Long

  'Attention! Works only with small ranges!!!

  Dim WS As Worksheet, R As Range

  Set WS = ActiveSheet

  'Return 0 if not filter is set

  If Not WS.AutoFilterMode Then Exit Function

  'Get the whole range from the filter

  Set R = WS.AutoFilter.Range

  'Remove headings

  Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)

  'Get the visible cells if any

  On Error GoTo ExitPoint

  Set R = R.SpecialCells(xlCellTypeVisible)

  'Return the row of first cell

  AutoFilterTopRow = R.Row

ExitPoint:

End Function

Function AutoFilterBottomRow() As Long

  'Attention! Works only with small ranges!!!

  Dim WS As Worksheet, R As Range, A As Range

  Set WS = ActiveSheet

  'Return 0 if not filter is set

  If Not WS.AutoFilterMode Then Exit Function

  'Get the whole range from the filter

  Set R = WS.AutoFilter.Range

  'Remove headings

  Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)

  'Get the visible cells if any

  On Error GoTo ExitPoint

  Set R = R.SpecialCells(xlCellTypeVisible)

  'Get the last area

  Set A = R.Areas(R.Areas.Count)

  'Return the row of last cell in this area

  AutoFilterBottomRow = A(A.Count).Row

ExitPoint:

End Function

Was this answer helpful?

1 person found this answer helpful.
0 comments

46 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2011-06-16T13:59:42+00:00

    When I copied it down to Row 10000 and AutoFilter'ed on the number 1, both functions worked fine. However, when I extended the range to 21000, both functions errored out. I'm guessing the 8192 non-contiguous areas problem is behind it for some reason.

    Right, that's why my code is much longer then yours.

    IMHO there is no other way as to split the autofilter range in handy parts, analyse them and combine them together afterwards.

    The bottleneck is to combine the parts, this toke the most time. But if we get the whole range, we can copy i.E. all visible cells to a second sheet.

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-06-15T19:27:55+00:00

    Here is an alternate GetFilteredRangeBottomRow function that should work for all situations (as a side benefit, it is much smaller than my other function)...

    Function GetFilteredRangeBottomRow()

      GetFilteredRangeBottomRow = Columns("A").Find(What:="*", SearchOrder:=xlRows, _

                                  SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    End Function

    As it turns out, we can use this same approach for getting both the top and bottom visible rows. Here ar e both functions (the bottom row one being a repeat of what I last posted)...

    Function GetFilteredRangeTopRow() As Long

      GetFilteredRangeTopRow = Columns("A").Find(What:="*", SearchOrder:=xlRows, _

                               SearchDirection:=xlNext, LookIn:=xlFormulas).Row

    End Function

    Function GetFilteredRangeBottomRow() As Long

      GetFilteredRangeBottomRow = Columns("A").Find(What:="*", SearchOrder:=xlRows, _

                                  SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    End Function

    I'm really glad you noted the address problem as it allows for these much more condensed functions.

    EDIT NOTE:

    It looks like there is a problem with this approach as well, though. It looks like the Find function is sensitive to number of non-contiguous areas it is asked to search through. I set up a simple test using this formula in A2...

    =MOD(ROW(),2)

    When I copied it down to Row 10000 and AutoFilter'ed on the number 1, both functions worked fine. However, when I extended the range to 21000, both functions errored out. I'm guessing the 8192 non-contiguous areas problem is behind it for some reason.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-06-15T18:58:18+00:00

    a.) Specialcells can handle only 8192 non continuous cells.

    I believe that is 8192 non-contiguous areas... I think each area can be composed of many contiguous cells. It is usually hard to reach that limit; but, of course, possible so I am glad you mentioned it. For those using XL2010 (or later I'm guessing), the 8192 limit has been removed.

    b.) The Address property of the range object returns only 255 chars.

    I'm not sure if b.) is correct, but in the reverse => "Range(String).Value = 0" the string is limited to 255 chars

    I had forgotten about that one... yes, your b) comment is correct as is your follow up comment. This could affect the functioning of my GetFilteredRangeBottomRow function for sure if the data range is long and non-contiguous enough (the GetFilteredRangeTopRow) function is not affected by this problem as no errors are generated when the Address property truncates the text.

    Here is an alternate GetFilteredRangeBottomRow function that should work for all situations (as a side benefit, it is much smaller than my other function)...

    Function GetFilteredRangeBottomRow()

      GetFilteredRangeBottomRow = Columns("A").Find(What:="*", SearchOrder:=xlRows, _

                                  SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    End Function

    Was this answer helpful?

    0 comments