Getting the Range Address of an AutoFiltered Range

Anonymous

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.

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

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

0 comments

Answer accepted by question author

Andreas Killer 144.1K Volunteer Moderator

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

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

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

0 comments

46 additional answers

Anonymous

2011-06-19T18:52:27+00:00

Andreas,

Thanks! I assume that the comment in your AutoFilterTopRow and AutoFilterBottomRow functions ("'Attention! Works only with small ranges!!!") refers to the 8192 non-contiguous ranges/areas limitation in Excel 2007 and previous versions that you and Rick have previously pointed out.

If I should ever have a situation where my filtered range exceeds the aforementioned limitation, I assume I should then use your original GetAutoFilterRange function (which calls your SplitRange function).

Are both of my assumptions correct?

Thanks again for all your help and all the time you invested in helping me. I greatly appreciate it.
Was this answer helpful?

0 comments No comments
Anonymous

2011-06-19T18:38:15+00:00

Rick,

I thought Andreas' latest "test" code demonstrated a problem with the GetFilteredRangeTopRow function.

If you take Andreas' most recent "test" code and combine it with your latest GetFilteredRangeTopRow code, it does in fact return row 12. However, row 12 is not part of the filtered range. To ensure no ambiguity, below is the complete code sample I used:

Sub Test()

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

'Bug #1

Cells.Clear

With Range("A1")

.Value = "Nr"

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

.CurrentRegion.AutoFilter 2, "<>1"

End With

MsgBox GetFilteredRangeTopRow & " is not a row of the autofilter: " & _

ActiveSheet.AutoFilter.Range.Address

End Sub

Function GetFilteredRangeTopRow() As Long

Dim HeaderRow As Long

On Error GoTo NoFilterOnSheet

With ActiveSheet

HeaderRow = .AutoFilter.Range(1).Row

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

SpecialCells(xlCellTypeVisible)(1).Row

End With

NoFilterOnSheet:

End Function

As much as I would prefer to have used the fewest lines of code in both functions, it appears that Andreas' AutoFilterTopRow and AutoFilterBottomRow functions (see his latest post above) account for all the issues and Excel limitations he previously raised.

I sincerely appreciate all the time you and Andreas have invested in helping me solve my problem. I can't thank both of you enough.
Was this answer helpful?

0 comments No comments
Anonymous

2011-06-19T17:11:20+00:00

@emerald77,

Andreas has uncovered a problem with the GetFilteredRangeBottomRow function. And, while his demonstration code used an older version of the GetFilteredRangeTopRow, it gave me a chance to spot a bit of sloppiness in my sheet referencing for my lasted version of that function (should not really matter much since we are using the ActiveSheet, but still....). Here are the two repaired functions that you should actually be using (but see my notes after them)...

Function GetFilteredRangeTopRow() As Long

Dim HeaderRow As Long

On Error GoTo NoFilterOnSheet

With ActiveSheet

    HeaderRow = .AutoFilter.Range(1).Row

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

                             SpecialCells(xlCellTypeVisible)(1).Row

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 = LastFilterRow

End With

NoFilterOnSheet:

End Function

NOTE 1: These functions deliberately return 0 if there is no AutoFilter active on the sheet (shows up in the MessageBox resulting from Andreas' Case 5 test). I did that because if filtering is off, then there is no top or bottom visible filtered row. I could have raised an error instead, but since there is no Row 0, I figured returning 0 would do just as well (you can test for that if your code needs to work with filtering either on or off).

NOTE 2: Andreas included a note in both of the functions that said " 'Attention! Works only with small ranges!!!" While I consider the word "small" to be somewhat misleading, I was perhaps remiss in not mentioning the size limitation in my functions. SpecialCells and Find both have a limit of 8192 non-contiguous ranges (although each of those individual ranges are obviously contiguous and, as such, have no limits other than what is imposed by the worksheet's size). Because of this, the minimum number of rows in a filter that might possibly cause a problem is 16384, but this would only be a problem if every other row of data met the filter criteria... and then, of course, you would be looking at 8192 rows of filtered data. Under normal circumstances, you could support far more than 16384 rows. I got the impression from the data you sent me that you won't come close to running up against this limitation. However, I want to thank Andreas for raising the issue as I should probably have thought to mention it on my own.
Was this answer helpful?

0 comments No comments