Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address


Any help or pointers are most graciously appreciated.

Regards,

David

On 1 Mar, 15:03, "Dave Mac" wrote:
Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address

Any help or pointers are most graciously appreciated.

Regards,

David


*bounce*

..specialcells won't work within a UDF when called from a cell on a worksheet.

Dave Mac wrote:

Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address

Any help or pointers are most graciously appreciated.

Regards,

David

--

Dave Peterson

On 1 Mar, 22:35, Dave Peterson wrote:
.specialcells won't work within a UDF when called from a cell on a worksheet.



Dave Mac wrote:

Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisibl e).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address

Any help or pointers are most graciously appreciated.

Regards,

David

--

Dave Peterson

Thanks Dave. I'll try a different approach.