name manager?

[Steve]

Morning all.
I'm trying to make a combo box and have come to realize that I'd probably be
better off using the name mgr to set my values.
I have a worksheet that's 4830 rows of data that I'd be using as my source.
Due to the type of data, its arranged in a manner that has a lot of spaces
between cells of data.

E.g. I have my column A data that varies between single rows, and having
gaps upwards of 20 rows. That data on column A is correlated to the data in
columns B-K, and the B-K data is what makes for the gaps.

So far, I tried setting the ListFillRange of the Combobox, for the entirety
of Column A, and it leaves me with a lot of blanks.
I'd really like to set up a named range, in the name mgr so that there are
no blanks in the dataset.
In attempting to do this, where my named range became:
=ShtNm!$A$1:$A$10,ShtNm!$A$15,ShtNm!$A$25:$A$26,....... varied
singlecelladdresses, and multiplerangeaddresses......ShtNm!$A$4825:$A$4830

This resulted in my hitting the 255 character limit real fast-- again, there
are 4830 rows of data. As well as trying to set up multiple named ranges.

My question is manifold.... and I hope concise enough to draw a suitable
response.

1- is there a way to set up a named range, or the combobox so that there are
no blanks in the Cbox-list?

I don't mind using a named range, but again-- blanks are not an option, and
due to the data's arrangement, I cannot modify without causing other, far
greater issues that'd render that dataset unusable.

Please advise if this isn't clear enough.


Thank you for your helps.
Best.
Steve

 

[OssieMac]

Hi Steve,

I suggest that you use Advanced Filter to copy a unique set of the records
to another worksheet and then name the range on the other worksheet and use
the named range as the RowSource. To copy advanced filter output to another
worksheet, you need to create a named cell on the other worksheet to get past
Excels idea that Advanced filter can't copy to another worksheet. You will
finish up with one blank cell in the range. You could get rid of it by
sorting the column before re-naming the range for the RowSource data.

Example code. (Not sorted because I don't know what version of xl you are
using and xl2007 is different to earlier versions.)

Sub Macro1()

'Name a cell on another worksheet
Sheets("Sheet2").Range("A1").Name = "MyList"

With Sheets("Sheet1")
.Columns("A:A") _
.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("MyList"), _
Unique:=True
End With

With Sheets("Sheet2")
.Range(.Cells(1, 1), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "CboSource"
End With

'Use CboSource as the RowSource

End Sub

 

[Steve]

Hi Ossie,
Sorry I didn't clarify.... I'm using Office 2007.

 

[OssieMac]

OK Steve because this is posted in an Excel programming forum I thought that
you would be able to look after the sort part but perhaps I thought wrong.
Anyway here is the code required to process and then apply the named range to
the combo box ListFillRange. All code that will work in either xl2007 or
earlier versions (Including the sort code).

You do not have to use the code between the asterisk lines to apply the
named range to the ComboBox ListFillRange. After you have run the code once,
you can actually apply the named range to the combo box ListFillRange in the
Properties dialog box. Just insert CboSource in the ListFillRange field.

Feel free to get back to me if you have any problems with it.

Note that you can hide the worksheet with the name range for ListFillRange.

Sub Macro1()

Dim oleObj As OLEObject 'Combo Box

'Name a cell on another worksheet
Sheets("Sheet2").Range("A1").Name = "MyList"

'Edit "Sheet1" to your sheet
With Sheets("Sheet1")
.Columns("A:A") _
.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("MyList"), _
Unique:=True
End With

'Edit "Sheet2" to your sheet
With Sheets("Sheet2")
'Sort the new range so blanks
'will be at bottom of list.
.Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Define name of new range for use
'in ComboBox or ListBox.
'Excludes column header and
'Blanks will be excluded.
.Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "CboSource"

End With

'*******************************************
'Use this code between asterisk lines to assign
'named range to a ComboBox on a worksheet.
With Sheets("Sheet1")
'Assign combo box to a variable
Set oleObj = .OLEObjects("ComboBox1")

'Assign CboSource as the ListFillRange
oleObj.ListFillRange = "CboSource"
End With
'******************************************

End Sub