CODEWITHSUNDEEP
excelvba
Bosch Man
Bosch Man

Reputation: 23

Find the next visible row

I am trying to write a function to return the next visible row in an autofiltered list.

In a sheet with an autofiltered range the code below returns a #VALUE error:

Function FindNextVisible(S As Range) As Range
Dim L As Range
Dim R As Range
Dim counter As Integer
counter = 1
Set L = Range(S, S.End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
For Each R In L
    counter = counter + 1
    If counter = 2 Then FindNextVisible = R
Next 
End Function

I suspect a beginners error...

UPDATE 1: Ok great advice. I can't use SpecialCells. Unfortunately the VBA is not strong in me and I'm having trouble with a Sub version.

Perhaps there is another way. I want to compare text between non-contiguous (due to filtering) rows, but I don't know how to provide the formula with a reference to the next visible row.

Upvotes: 2

Views: 5678

Answers (1)

user2140261
user2140261

Reputation: 7993

Tthe following should accomplish what you are looking for.

Public Function NextVisibleCell(Range As Range) As Range
Application.Volatile
Dim i As Long
Set Range = Range.Cells(Range.Rows.Count, Range.Columns.Count)
For i = 1 To Rows.Count - Range.Row
    If Not Range.Offset(i).EntireRow.Hidden Then
        Set NextVisibleCell = Range.Offset(i)
        Exit Function
    End If
Next i
End Function

Upvotes: 3

Related Questions