Building a named range using vba

[hallingh]

Hi all. I have a sheet that uses two drop down lists. I'm using a named range to populate each of the lists. However, we have been using this program for a while and lists are starting to get too long. So, I want to allow the user to filter the lists down, so I have added a few drop downs that the user can choose from that will narrow down the choices in hte original drop downs. An example of one of these new drop downs is "Amount of time with company," with the choices being "0-6 months," "6-12 months," etc. If the user selected "0-6 months" I would want the original drop downs to display only people who have been with the company for 0-6 months.

So, I've decided to put a button on the sheet that will run a sub that will populate the named range only with values that fit the criteria. But, I have no idea how to build a named range using vba. Can anyone provide me with an example or a website that goes into detail on how to construct a named range using vba?

I tried a couple google searches but didn't find anything all that useful. Any help at all that you can give me is greatly appreciated. Thanks!

Hank

 

[hallingh]

Hmmm maybe I was too quick to assume that it is possible to add to a named range using vba. Does anyone at least know if this is possible or has anyone actually done this?

 

[Gary McMaster]

it is possible to add to a named range using vba.

Try something like this:

Dim oCell As Range
Dim oRange As Range

Set oRange = ActiveSheet.Range("B6:D12")

oRange.Name = "Test"

For Each oCell In ActiveSheet.Range("Test")
    oCell.Value = "Test"
Next oCell

 

[hallingh]

Thanks a lot for the code. Is there any way I can use this repeatedly? I have all of my data stored in a large range from another sheet in my workbook. I'm thinking about first having my sub search through the data and, if it finds values that satisfay the criteria the user wants, it will put those values into a sperate range. I then wanted to set the named range equal to the range on the second sheet. I figured doing it this way would make it easy to show the correct values without having a bunch of blank rows in between.

My problem is, wouldn't dimming a range with the same name and giving it a value more than once bring up an error? Will I have to Redim it instead?

Thanks again for the code and the help. I was working on another issue today, but I'll try to put some code together tomorrow to give you a better idea of what I want to do.

Thanks again for hte code.

Hank

 

[Gary McMaster]

My problem is, wouldn't dimming a range with the same name and giving it a value more than once bring up an error? Will I have to Redim it instead?

Not sure what you mean by the above.

You can use the same range variable over and over to refer to different named ranges without "Redim" though I'm not sure I really know what you mean by that.

Here's another sample that I hope will demonstrate some of the things you can do with the names. You can paste this into a standard module of a new workbook to try it. I hope it helps.

Gary

Public Sub Test()

Dim oRange1 As Range
Dim oRange2 As Range
Dim oName As Name

For Each oName In ThisWorkbook.Names
    oName.Delete
Next oName

ActiveSheet.Range("B:B").Name = "ColumnB"
ActiveSheet.Range("D:D").Name = "ColumnD"
ActiveSheet.Range("2:2").Name = "Row2"

'ActiveSheet.Range(~Get address from text in cell A1~).Name = ~Get range name from text in cell B1~
'ActiveSheet.Range(~Get address from text array~).Name = ~Get range name from text array~
'ActiveSheet.Range(~Get address from other text source~).Name = ~Get address from other text source~
<get address="" from="" text="" in="" cell="" a1=""><get range="" name="" from="" text="" in="" cell="" b1=""><get address="" from="" text="" array=""><get range="" name="" from="" text="" array=""><get address="" from="" other="" text="" source=""><get address="" from="" other="" text="" source="">

Set oRange1 = ActiveSheet.Range("A4:D4")
oRange1.Name = "PartialRow4"
Range("PartialRow4").Interior.ColorIndex = 6 'Yellow

'Reuse oRange1 for different range
Set oRange1 = ActiveSheet.Range("ColumnB")
oRange1.Interior.ColorIndex = 3 'Red

ActiveSheet.Range("ColumnD").Interior.ColorIndex = 4 'Green

Range("Row2").Interior.ColorIndex = 5 'Blue

For Each oName In ThisWorkbook.Names
    Range(oName.Name).Font.Bold = True
    Debug.Print oName.Name & vbTab & oName.RefersTo
Next oName

'Reuse oRange1 again
Set oRange1 = Range("PartialRow4")

Set oRange2 = Application.Intersect(Range("ColumnD"), oRange1)

MsgBox oRange2.Address

End Sub

</get></get></get></get></get></get>