You are here:

Excel/VBA to create Dynamic Named Range

Advertisement


Question
QUESTION: Hi
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
Charlie

ANSWER: Charlie,

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:

=OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B$4:$B$33),COUNTA(Sheet1!$B$4:$AE$4))

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.  

Sub abc()
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) & "))"

End Sub

hopefully that is what you want.

--
Regards,
Tom Ogilvy


---------- 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.

Answer
Charlie,


> 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 twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.