Has anyone considered a tweaked Ctrl-Shift-F3 macro?

[bubba2413]

I use a lot ... LOT of dynamic named ranges. Ctrl-Shift F3 is a great start, but it sure would be nice to have a Checkbox option to make the named range dynamic.

Consider data in cells A1:D4. Highlighting the data range, I can use Ctrl-Shift-F3 to create named ranges with any/all/none names defined from the: Top Row, Left Column, Bottom Row or Right Column. Wonderful!

Generally, I only use the Top Row for names... but thats kind of beside the point. What I'd really like to see is a fifth checkbox named something like "Dynamic". This way, (for names in the top row) instead of a name like:
List_01 with a definition of: =Sheet1!$A$2:$A$4 I get something like
List_01 with a definition of: = offset(Sheet1!$A$1,1,0,Counta($A:$A)-1)

Currently I'm having to go back and manually tweak all the definitions, but it would sure be nice to have the option with the Ctrl-Shift-F3 of AUTOMATICALLY making them dynamic ranges.

I know it would take VBA to do this, but the code is eluding me at the moment.

Thoughts? any help?

 

[p45cal]

If it's just eluding you at the moment, then this might get you started:

Sub blah()
 '   ActiveWorkbook.Names.Add Name:=Selection.Value, RefersTo:="=OFFSET('" & Selection.Parent.Name & "'!" & Selection.Address & ",1,0,COUNTA('" & Selection.Parent.Name & "'!" & Selection.EntireColumn.Address & ")-1)"
    ActiveWorkbook.Names.Add Name:=Selection.Value, RefersTo:="=OFFSET('" & Selection.Parent.Name & "'!" & Selection.Address & ",1,0,COUNTA('" & Selection.Parent.Name & "'!" & Range(Selection.Offset(1), Cells(Rows.Count, Selection.Column)).Address & "))"
End Sub

The commented out line follows your example in that it uses a count of the entire column.
The other line uses the entire column beneath the selected single cell.
They use the selected cell contents as the name - watch out, because there may be invalid characters in it which ctrl+shift+F3 circumvents, but this doesn't.
It's only been designed at the moment for one cell being selected before running it.

 

[bubba2413]

Excellent! and thanks for the quick response!

This would be great for a simple macro for use when highlighting only the naming cell. (given the caveat re: illegal characters, which I'll handle with a couple of Substitute calls)

What I'm really trying to do is mimic the behavior of the CTRL-SHIFT-F3 function. To that end, I've already created a userform with the additional checkbox and am working on the named range definition function.

Currently, I'm taking the selection.address and parsing out the top/bottom/left/right rows and columns, respectively. Given that the selection COULD only be a single cell, I'm tweaking the selection.address such that it is always in the format CR:CR, then using a couple split functions to get the desired row and column identiers.

I'm thinking that I can 'walk' through the name definitions using the selection.rows.count and selection.columns.count.

Thoughts?

 

[p45cal]

Yes,

Sub blah()
Set xxx = Selection

'top row cells:
For Each cll In xxx.Rows(1).Cells
  'cll.Select
  ActiveWorkbook.Names.Add Name:=cll.Value, RefersTo:="=OFFSET('" & cll.Parent.Name & "'!" & cll.Address & ",1,0,COUNTA('" & cll.Parent.Name & "'!" & Range(cll.Offset(1), Cells(Rows.Count, cll.Column)).Address & "))"
Next cll

'left side cells
For Each cll In xxx.Columns(1).Cells
  cll.Select  'replace with Names.Add line of your design.
Next cll

'bottom row cells:
For Each cll In xxx.Rows(xxx.Rows.Count).Cells
  cll.Select 'replace with Names.Add line of your design.
Next cll

'right side cells:
For Each cll In xxx.Columns(xxx.Columns.Count).Cells
  cll.Select 'replace with Names.Add line of your design.
Next cll
End Sub

now you have to surround each of the loops with its own If..End If checking whether the corresponding check box is checked.

 

[bubba2413]

Excellent! Thanks.. I'm there.. just had a brain fart and couldn't get started.