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-14T17:54:52+00:00

Thanks for the additional code! I really appreciate it.

Prior to seeing it, however, below is what I wrote:

Function GetFilteredRangeTopRow()

Dim Rng As Range, RngAddr As String

Set Rng = ActiveSheet.AutoFilter.Range

Set Rng = Intersect(Range(Rng(1).Offset(1), Cells(Rows.Count, "A")), _

Rng.SpecialCells(xlCellTypeVisible))

RngAddr = Rng.Address(0, 0)

RngAddr = Mid(RngAddr, 2, InStr(RngAddr, ":") - 2)

GetFilteredRangeTopRow = Val(RngAddr)

End Function

Then, in my main program, I have the following code snippet:

Dim BeginRow As Integer, EndRow As Integer

BeginRow = GetFilteredRangeTopRow

EndRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

It appears to work every time. Thanks again!
Was this answer helpful?

0 comments No comments
Anonymous

2011-06-14T16:51:24+00:00

FYI, even when I move the header row to row 3 (as an example), your macro still returns the correct first row and last row of the displayed (i.e., filtered) range.

It was designed to do that... once Andreas made clear my need to exclude the header row, it became obvious that I need to skip over any blank rows immediately after the header.

I then use a combination of the MID and INSTR functions to obtain the two row numbers (which I assign to two integer variables for further processing) from the returned range.

Here is my code modified to pull out the addresses and row numbers for the first and last cells returned by my original code, in case you can make use of this in anyway...

Sub GetVisibleRange()

Dim Rng As Range, Addresses As String, AddressCells() As String

Dim FirstVisibleRow As Long, LastVisibleRow As Long

Dim FirstVisibleCell As Range, LastVisibleCell As Range

Const FilteredColumn As String = "A"

Set Rng = ActiveSheet.AutoFilter.Range

Set Rng = Intersect(Range(Rng(1).Offset(1), Cells(Rows.Count, FilteredColumn)), Rng.SpecialCells(xlCellTypeVisible))

Addresses = Rng.Address(0, 0)

AddressCells = Split(Replace(Addresses, ":", ","), ",")

Set FirstVisibleCell = Range(AddressCells(0))

Set LastVisibleCell = Range(AddressCells(UBound(AddressCells)))

FirstVisibleRow = FirstVisibleCell.Row

LastVisibleRow = LastVisibleCell.Row

MsgBox "First Cell Address: " & FirstVisibleCell.Address(0, 0) & vbLf & _

         "First Cell Row: " & FirstVisibleRow & vbLf & _

         "Last Cell Address: " & LastVisibleCell.Address(0, 0) & vbLf & _

         "Last Cell Row: " & LastVisibleRow

End Sub

Thanks again to you and Andreas for all your help.

I'm sure I speak for Andreas as well as myself when I say, "You are quite welcome!"
Was this answer helpful?

0 comments No comments
Anonymous

2011-06-14T15:34:29+00:00

FYI, even when I move the header row to row 3 (as an example), your macro still returns the correct first row and last row of the displayed (i.e., filtered) range. I then use a combination of the MID and INSTR functions to obtain the two row numbers (which I assign to two integer variables for further processing) from the returned range.

Thanks again to you and Andreas for all your help.
Was this answer helpful?

0 comments No comments