Excel/VBA to create Dynamic Named Range
I would like to be able to make a selection on a sheet. Say A1:d20, and then use a macro to create that range as dynamic, using the top left hand cell (A1 in this case)but would vary, but still the top left hand.
XL2000 I am using.
Lots of code out there but nothing that uses the top left cell etc. Need Dynamic both ways by column and rows.
Are you able to assist. Appreciated.Thanks
Selection.Name = "MyName"
will create the named range MyName and it will refer to the selected celss
if you want to refer to the top left cell then
Dim r as Range
set r = Selection(1)
r.name = "MyName"
You said you wanted a dynmaic named range, but you haven't said what to base it on. Are you looking to use a formula to determine the extent of the range. Something like this formula:
In the above, I use B4 as the top left cell of the defined name. Then I count the number of filled cells in B4:B33 to determine the number of rows in the defined name. I then count the number of filled cells in B4:AE4 to determine the number of columns.
To build this defined name, I used the following code:
Because I don't know you situation, I only look out 30 cells in both the row and column direction to count the number of filled cells. The count is done on the left most column of the selection and the top row of the selection.
Dim r As Range, rRws As Range, rCols As Range
Set r = Selection
Set rRws = r(1).Resize(30, 1)
Set rCols = r(1).Resize(1, 30)
ThisWorkbook.Names.Add Name:="MyHouse", RefersTo:="=Offset(" & r(1).Address(1, 1, xlA1, True) & _
",0,0,COUNTA(" & rRws.Address(1, 1, xlA1, True) & "),COUNTA(" & rCols.Address(1, 1, xlA1, True) & "))"
hopefully that is what you want.
---------- FOLLOW-UP ----------
QUESTION: Hi Tom
Thanks for your response. The code looks good but It does not seem to select as it should. I can send a WB with some comments on Sheet1 and in VB Editor. Tried to attach below but does not accept .xls
Where could I email the WB.
In the code it should name the range as contents of top left hand cell, say if that is "aaa" in the cell that is what the range would be called.
If the first range is say 4 columns wide by 50 rows, it could expand to 6 columns wide by 200 rows.
> If the first range is say 4 columns wide by 50 rows, it could expand to 6 columns wide by 200 rows.
So you would have to explain why it would do that.
>In the code it should name the range as contents of top left hand cell,
but you never said that. What you said was
> using the top left hand cell (A1 in this case)but would vary, but still the top left hand.
so you say using, but I understood that to mean you wanted to use it as the base cell for the dynamic range (since you didn't say anything using the value in the cell as the name of the range).
So yes, if you want to send me a clearly stated requirement with your workbook I can try to give you some help.
send it to firstname.lastname@example.org