First Row of AutoFilter Data

[Igwiz]

Hi there,
What is the code for finding the first row of an autofiltered range? ie if I have data in rows 1 to 1000 and I autofilter it so that the first row shown is 534, I want to return 534 so I can then access data in that row.
Many thanks.

Ig

 

[Zathras]

Here is one way:
[blue]

Option Explicit

Sub test()
  MsgBox "First visible row = " & FirstVisibleRow(Range("A1:X1000"))
End Sub

Function FirstVisibleRow(AutoFilterTable As Range) As Long
Dim rng As Range
Dim nFirstRow As Long
Dim nLastRow As Long

[green]

  ' Find first and last rows of table

[/color]

  With AutoFilterTable
    nFirstRow = .Row + 1
    nLastRow = .Rows.Count + .Row - 1

[green]

    ' Find first visible row within auto filter table

[/color]

    Set rng = Intersect(.SpecialCells(xlCellTypeVisible), _
                      Range(nFirstRow & ":" & nLastRow))
    FirstVisibleRow = rng.Row
    Set rng = Nothing
  End With
End Function

[/color]
Instead of hard-coding the A1:X1000 as in this sample, it would be better to use a named range.

 

[xlbo]

Here's a shorter way - assumes filter headers in row 1, starting in column A - this will returnt he 1st visible row:

mRow = Range("A1&quot.CurrentRegion.SpecialCells(xlCellTypeVisible).Areas(2).Row

watch for word wrap - it should all be on 1 line

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Zathras]

Geoff, unfortunately it's a little too short. It doesn't handle the case where the first data row is among the filtered shown, and fails miserably when showing all the data.

If a shorter way is wanted, give a range name to the data area only (do not include the header row) and then you should be able to use

With Range("FILTERDATA")
  nRow = Intersect(.Columns(1), .SpecialCells(xlCellTypeVisible)).Row
End With

[/color]
But usually it is desirable to include the column headings in a list range, so this way might require two range names to be set up and kept in sync.

 

[xlbo]

Fair play Z - I guess I just didn't think that anyone would run it when there is no filter in place... ;-)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244