2

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.

Community
  • 1
  • 1
Bosch Man
  • 23
  • 1
  • 4

1 Answers1

3

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
user2140261
  • 7,855
  • 7
  • 32
  • 45
  • + 1 This does get the value of the next visible row. – Siddharth Rout Feb 06 '14 at 17:14
  • This is a very imp point that OP wants to handle `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.` – Siddharth Rout Feb 06 '14 at 17:16
  • You *can* return a reference to a range from a UDF... `Function Nxt(r As Range) As Range: Set Nxt = r.Offset(1, 0): End Function` If you feed the return value from this into (eg) `OFFSET()` it seems to work just fine. – Tim Williams Feb 06 '14 at 17:17
  • @TimWilliams: True and then OP can use your [THIS](http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257) code to compare the rows? – Siddharth Rout Feb 06 '14 at 17:18
  • @TimWilliams You are correct not sure why I thought you couldn't, Updated Answer to reflect. – user2140261 Feb 06 '14 at 17:21