this is mad... :confused: am trying to write a function to return the first cell
in a filtered list...
but while developing it I came across this problem...

these two pieces of code are exactly the same, except one is in a
function... you'll noticed ive commented the line out that 'should' return the second SpecialCells(xlCellTypeVisible) and should return the result a want.

The problem is...
the subroutine debug.print returns all my filtered cells, the function debug.print returns everything!!


any clues whats happening???


Function fRow() As String
a = 0
For Each c In
Sheets(1).AutoFilter.Range.SpecialCells(xlCellType Visible)
a = a + 1
'If a = 2 Then fRow = c.Value
Debug.Print c.Value
Next
End Function
'------------------------------------
Sub fRow2()
a = 0
For Each c In
Sheets(1).AutoFilter.Range.SpecialCells(xlCellType Visible)
a = a + 1
'If a = 2 Then fRow = c.Value
Debug.Print c.Value
Next
End Sub

Having removed the space between type and visible, which I'm sure doesn't exist in your original code, both listed my filtered cells only.

Brian

Having removed the space between type and visible, which I'm sure doesn't exist in your original code, both listed my filtered cells only.

Brian


You're correct Brian, my code doesnt have the space. Thanks for trying it out.

I remain equally frustraited... the function returns everything for me... :confused:

As I merely copied and pasted your code into a module I don't know why it works for me and not you.
I have now modified the code to just pick up the value when a=2 both work, perhaps you might like to give them a try.

Brian


Function frow() As String
a = 0
For Each c In Sheets(1).AutoFilter.Range.SpecialCells(xlCellType Visible)
a = a + 1
If a = 2 Then GoTo exit1
Next
exit1:
frow = c.Value
Debug.Print c.Value

End Function

Sub fRow2()
Dim frow As Variant
a = 0
For Each c In Sheets(1).AutoFilter.Range.SpecialCells(xlCellType Visible)
a = a + 1
If a = 2 Then GoTo exit1
Next
exit1:
frow = c.Value
Debug.Print c.Value
MsgBox frow

End Sub