SpecialCells and Names
 

I have created the following Function and tested it with some code

Public Function MyCells(ByVal MyRange As Range, MyOffset As Integer)

Set MyCells = MyRange.SpecialCells(xlCellTypeConstants, xlLogical).Offset(0,
MyOffset)

End Function

TEST CODE

Sub test()

MsgBox MyCells(ActiveSheet.Range("I1:I1000"), -2).Address

End Sub

The msgbox displays - $G$6,$G$9,$G$13:$G$88 which is the desired result.

However, when I create a name which refers to this function (using the same
inputs) and then use that name in validation, I get all entries from G1 to
G88 in the dropdown box.

Can anyone help with this??
--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone

SpecialCells and Names
 

Drop down box can't use a dicontiguous range. You would have to either fill
it with additem or use formulas/code to build your range in another area and
refer to that.

--
Regards,
Tom Ogilvy


"Richard Reye" wrote in message
...
I have created the following Function and tested it with some code

Public Function MyCells(ByVal MyRange As Range, MyOffset As Integer)

Set MyCells = MyRange.SpecialCells(xlCellTypeConstants,
xlLogical).Offset(0,
MyOffset)

End Function

TEST CODE

Sub test()

MsgBox MyCells(ActiveSheet.Range("I1:I1000"), -2).Address

End Sub

The msgbox displays - $G$6,$G$9,$G$13:$G$88 which is the desired result.

However, when I create a name which refers to this function (using the
same
inputs) and then use that name in validation, I get all entries from G1 to
G88 in the dropdown box.

Can anyone help with this??
--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then
beat
you with experience" - someone