Union of Named Ranges

**justinv** · 11-08-2012, 10:22 AM

Having some issues. Simply want to combine 2 named ranges into one on a cell change event. Cell change event is no problem. I can't get the union to work. I have this code on sheet 3. What am I doing wrong?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)   'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$D$1" Then
        Dim std, usr, all As Excel.Range
        With ActiveWorkbook
            Set std = Names("stdprojects").RefersToRange
            Set usr = Names("users").RefersToRange
            Set all = Union([std], [usr])
        End With
        ActiveWorkbook.Names.Add Name:="AllEvent", RefersTo:=all
   End If
End Sub

**shg** · 11-08-2012, 11:17 AM

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$1" And _
       Not IsEmpty(Target.Value) Then
        Me.Parent.Names.Add Name:="AllEvent", _
                            RefersTo:=Range("stdprojects, users")
    End If
End Sub

**justinv** · 11-08-2012, 12:32 PM

Getting a 1004 error. "Method 'Range' of object '_Worksheet' failed" on this line

Me.Parent.Names.Add Name:="AllEvent", _
                            RefersTo:=Range("stdprojects, users")

Should I have this in a worksheet or in a seperate Module?

**Norie** · 11-08-2012, 12:35 PM

Remove the square brackets.

**justinv** · 11-12-2012, 11:27 AM

Removing the square brackets didn't work. I had it like that before, but I thought I would try them.

Any ideas why your code won't work in my spreadsheet shg?

Any one else worked on something like this in the past? Is there an easier way to get 2 dynamic ranges into a drop down validation cell? I'm just sitting scratching my head now.

**shg** · 11-12-2012, 12:02 PM

Post a workbook.

**justinv** · 11-12-2012, 12:22 PM

My actual workbook I won't post here as it is becoming huge and I don't want to confuse anyone. Here is basically what I'm trying to do. I used the same code and getting the same errors.

Thanks for the help everyone

union trial.xlsm

**mikerickson** · 11-12-2012, 12:28 PM

You could use

ActiveWorkbook.Names.Add Name:="AllEvent", RefersTo:= "=" & all.Address(,,,True)

or use Excel's native union operator (the comma) in the defintion of AllEvent

Private Sub Worksheet_Change(ByVal Target As Range)
   'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$D$1" Then
        ActiveWorkbook.Names.Add Name:="AllEvent", RefersTo:= "=stdprojects,users"
   End If
End Sub

This approach might remove the need for a change event.

**Andy Pope** · 11-12-2012, 12:39 PM

But DV will not work with a non contiguous range.

You would need to construct a range of contiguous information.
See DV0014 - Combine Multiple Lists into One
http://www.contextures.com/excelfiles.html

**justinv** · 11-12-2012, 12:52 PM

mikerickson, having trouble using the resulting named range in a data validation cell as that has a maximum of 1 column allowed. Hence why I was attempted to use the union. But I didn't fully understand the way the named ranges worked until now.

*face palm* Of course. Thank you Andy. I'll rework this sheet to create a combined list on a hidden sheet and feed the data validation from there. Much easier solution.