Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specialcells | Charts and Charting in Excel | |||
SpecialCells(xlCellTypeLastCell) | Excel Programming | |||
SpecialCells(xlCellTypeFormulas) | Excel Programming | |||
AutoFilter /specialcells | Excel Programming | |||
specialcells(xlcelltypeblanks) | Excel Programming |