.SpecialCells(xlCellTypeVisible) not working in function?

[larsa]

Hello,
I have a filtered area where I want to repeat/show the first visible cell in another cell above (so that I can always refer to this destination).

I've tried to make a function for doeing this, but it does not work... it only return the first cell disregarding it beeing visible or not. I've done checked the same in a procedure, and then it returns only the visible cell.

Here's my code:
Function nextVisibleCell(rng As Range) As Variant
' Application.Volatile
nextVisibleCell = Range(rng.Offset(1, 0), rng.End(xlDown)).SpecialCells(xlCellTypeVisible).Cells(1)
End Function

I want it to always run whenever the filter is changed.

any suggestions?
regards,
Lars A.

-------------------
This is how it looks like, I'd like to return/show val2 since col 1 is filtered with val2, but my function returns val1. There can be more than one row with val1, val2, val3 etc, but I like to return/show the first apperances.

[TABLE="width: 146"]
<tbody>[TR]
[TD]val2[/TD]
[TD][/TD]
[TD]val1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]head1[/TD]
[TD][/TD]
[TD]head1[/TD]
[/TR]
[TR]
[TD]val2[/TD]
[TD][/TD]
[TD]val1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]val2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]val3[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Hi,
You can't use specialcells in a udf I'm afraid - you'll have to loop through the cells and check if the row is visible.

 

[larsa]

Thank you very much for the quick reply!

As you may understand I'm not a experienced VBA user, so:
Is there any way that I can know what is working where???

regards,
Lars A.

 

[RoryA]

You would need a loop like this:

Function nextVisibleCell(rng As Range) As Variant
   Dim rngCell                As Range
   Application.Volatile True
   nextVisibleCell = "No visible cells"
   For Each rngCell In Range(rng.Offset(1, 0), rng.End(xlDown))
      If rngCell.EntireRow.Hidden = False Then
         nextVisibleCell = rngCell.Value
         Exit For
      End If
   Next rngCell
End Function

 

[larsa]

Thank you very very much!!!

Works like a dream.

regards,
LA