Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-11-2018, 05:59 AM
cosmicyes cosmicyes is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jan 2018
Posts: 8
cosmicyes is on a distinguished road
Default Find first rownumber in a filtered list

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!
Reply With Quote
  #2  
Old 07-11-2018, 08:08 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 574
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

will leave to others.

Last edited by NoSparks; 07-11-2018 at 04:52 PM.
Reply With Quote
  #3  
Old 07-11-2018, 03:54 PM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 123
p45cal is on a distinguished road
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
  #4  
Old 07-11-2018, 10:30 PM
cosmicyes cosmicyes is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jan 2018
Posts: 8
cosmicyes is on a distinguished road
Default

Thank you @p45cal.
This solved my issue!


Also thanks to @NoSparks.
Reply With Quote
Reply

Tags
range, specialcells

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 05:13 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft