name manager?
 

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

name manager?
 

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

--
Regards,

OssieMac


"Steve" wrote:

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

name manager?
 

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


"OssieMac" wrote:

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

--
Regards,

OssieMac


"Steve" wrote:

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