Find first rownumber in a filtered list

cosmicyes · #1 07-11-2018, 05:59 AM

Hi,

I have got a filtered list in my worksheet "Daten" and the first visible row for example has the number 16.
I´d like to get this value (16) by a little VBA Script.

This is what I have done so far:

Code:

Dim VisibleRange As Range

Dim FirstVisibleRow As Single
   
Set VisibleRange = Worksheets("Daten").AutoFilter.Range.SpecialCells(xlCellTypeVisible)
 
FirstVisibleRow = VisibleRange.Cells(1).Row

Result:
FirstVisibleRow is 1, not 16.

What is my mistake?

Thanks a lot in advance!

NoSparks · #2 07-11-2018, 08:08 AM

will leave to others.

p45cal · #3 07-11-2018, 03:54 PM

Quote:

Originally Posted by cosmicyes

FirstVisibleRow is 1, not 16.
What is my mistake?

You're probably seeing the Autofilter header row which is never filtered out by the autofilter. The header row is included in the Autofilter.range.
Try:

Code:

Dim VisibleRange As Range, FirstVisibleRow As Long ', LastVisibleRow As Long
With Worksheets("Daten")
  Set DataBodyRange = Intersect(.AutoFilter.Range, .AutoFilter.Range.Offset(1)) 'presumes there is an autofilter in place.
  On Error Resume Next    'in case all rows are hidden
  Set VisibleRange = DataBodyRange.SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  If Not VisibleRange Is Nothing Then
    FirstVisibleRow = VisibleRange.Row
'    Set lastArea = VisibleRange.Areas(VisibleRange.Areas.Count)
'    LastVisibleRow = lastArea.Cells(lastArea.Cells.Count).Row
  End If
End With

If all rows are filtered out the result for first and last visible rows is the impossible row number zero.

cosmicyes · #4 07-11-2018, 10:30 PM

Thank you @p45cal.
This solved my issue!

Also thanks to @NoSparks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
for visible cells (filtered), how find value lowest than 4.00 and return the cell number	murfy69	Excel	6	08-03-2017 12:03 AM
Creating a Custom List that Updates when one of the data fields is filtered	anthrus	Excel	1	11-13-2014 11:58 PM
Find - Replace Macro using a table list	mdw	Word	0	08-01-2013 04:36 PM
How do I know when mail is filtered	Andoheb	Outlook	2	01-09-2012 04:43 PM
Find in alphabetical order list	14spar15	Word	1	12-03-2011 03:01 PM