I have been using a macro that i now find defective. The macro goes to a range, then does a filter and goes to first row of filtered data. What the recorded macro had was "Range("H2").Select" Now with different data the filter should find the first
row in H with data so I might want to be in H20 or H1985. I can't seem to find the correct way to do that.
I have been using a macro that i now find defective. The macro goes to a range, then does a filter and goes to first row of filtered data. What the recorded macro had was "Range("H2").Select" Now with different data the
filter should find the first row in H with data so I might want to be in H20 or H1985. I can't seem to find the correct way to do that.
Add the following function to your code module:
Function findFirstVisibleFilteredCell(StartCell As Range)
Dim aRng As Range
Set aRng = StartCell.CurrentRegion 'aRng also contains the header row
Set aRng = aRng.Offset(1, 0).Resize(aRng.Rows.Count - 1) 'and now it doesn't
Set findFirstVisibleFilteredCell = aRng.SpecialCells(xlCellTypeVisible).Cells(1, 1)
End Function
Now, use it as
findFirstVisibleFilteredCell(Range("H2")).Select
Tushar Mehta (MVP Excel 2000-2015) Excel and PowerPoint tutorials and add-ins www.tushar-mehta.com
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
