Define Name

[rml]

I have a defined NAME(e.g. GROUP1) of a group of Cells in a table. Within
this group of cells, I had defined another NAME (e.g. GROUP_1) for those
blank cells that I gathered. My problem is how to make dynamic changes on
the referred cells of GROUP_1, say if I place value in one blank cell, the
GROUP_1 will be updated.

GROUP1 : A1:J5
GROUP_1 : A2,J4 <BLANK CELLS>

Editing the GROUP1, I delete the content of B1 and B2...
Hence, I need GROUP_1 to recognize the changes
GROUP_1 must be : A2,J4,B1,B2

I hope this is not another unsolved problem in our forum....

thanks a lot...
rml

 

[Gary''s Student]

Assuming that GROUP1 and GROUP_1 have already been defined. We need two
pieces of code:

1. worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("GROUP1")) Is Nothing Then
Else
Call main
End If
End Sub

so if any changes are madein GROUP1, main() is called

2. In a standard module:

Sub main()
Dim r As Range
Dim rr As Range
Dim s As String
With ActiveWorkbook
c = .Names.Count
If c > 0 Then
For i = 1 To c
If .Names(i).Name = "GROUP_1" Then
.Names("GROUP_1").Delete
Exit For
End If
Next
End If
For Each r In Range("GROUP1")
If IsEmpty(r) Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
s = rr.Address(ReferenceStyle:=xlR1C1)
MsgBox (s)
..Names.Add Name:="GROUP_1", RefersToR1C1:="=Sheet1!" & s
End With
End Sub

The code checks to see if GROUP_1 exists. If it exists, it is deleted. The
code then re-constructs GROUP_1 from scratch, UNIONing in empty cells. The
Named Range is then re-added to the workbook with the most current empty cell
set.

 

[rml]

CONGRATS...you're a wizard....hope u can generate more dynamic questions that
i will post sooner....thanks a lot.

 

[rml]

Gary, could you extend help a little bit....
I forgot that I also have a defined name GROUP_2,within the GROUP1,which
contains values, How can we do dynamic change to GROUP_2... I dont know
VBE...thanks again.

 

[Gary''s Student]

Tell me more about GROUP_2.
--
Gary's Student

Gary, could you extend help a little bit....
I forgot that I also have a defined name GROUP_2,within the GROUP1,which
contains values, How can we do dynamic change to GROUP_2... I dont know
VBE...thanks again.

 

[rml]

e.g.
GROUP1 : A1:J10
GROUP_1 : blank cells within GROUP1
GROUP_2 : non-blank cells within GROUP1
GROUP1 = GROUP_1 + GROUP_2

thanks
rml