Getting the Range Address of an AutoFiltered Range

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

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