Thread: [Solved] Find first rownumber in a filtered list
View Single Post
 
Old 07-11-2018, 03:54 PM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
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.
Reply With Quote