VBA Create Dynamic Named Range

**hobbiton73** · 10-26-2013, 09:48 AM

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to create a sheet and add named ranges to each column.

Please Login or Register  to view this content.

The problem I have is that the ranges are not dynamic i.e the end of the range is row 65536, rather than the last row containing data.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about adapting this so the last row of the range is the last row containing data?

Many thanks and kind regards

**JBeaucaire** · 10-26-2013, 10:32 AM

Maybe of the newsheet has data:

Please Login or Register  to view this content.

If the newsht has no data, then you need to create dynamic formulas using OFFSET() that will expand automatically as you add data to the new sheet.

**stnkynts** · 10-26-2013, 10:35 AM

I think it is this line right here that is messin you up:

Please Login or Register  to view this content.

Try this instead

Please Login or Register  to view this content.

Note: It would be a good idea to dim all your variables

**hobbiton73** · 10-26-2013, 10:46 AM

Hi @stnkynts, thank you for taking the time to reply to my post and for the code change suggestion.

I've tried your suggestion, and unfortunately, although the code runs without errors, the range only extends from the header row to the next three rows of data, rather than the full range, so I'm not really sure where I've gone wrong.

Many thanks and kind regards

**hobbiton73** · 10-26-2013, 10:47 AM

Hi @JBeaucaire, thank you for taking the time to reply to my post and for your suggested approach.

Many thanks and kind regards.

**JBeaucaire** · 10-26-2013, 11:02 AM

Which column in your data set always has data in it? Use that column to search for the "Last Row" and edit the LR = part of the code I added to use that column instead of column A like I did.

**hobbiton73** · 10-26-2013, 11:24 AM

Hi @JBeaucaire, thank you very much for your continued help with this. I've made the changes as you suggest, but unfortunately this doesn't work. The range that is created for each column only contains the cell reference for the header row.

Many thanks and kind regards

**Norie** · 10-26-2013, 11:59 AM

What data is on the active sheet when the code runs?

**hobbiton73** · 10-26-2013, 01:01 PM

Hi @Norie, thank you for taking the time to reply to my post.

The only data on the sheet when the code runs are the column headings. I know this is probably very much a beginners question, but I was under the impression that a dynamic range expands to accommodate any data once the range has been set. Could you tell me please, is this not the case?

Many thanks and kind regards

**Norie** · 10-26-2013, 01:07 PM

If you want dynamic named ranges that expand then you would need to use formulas.

What you are creating are static named ranges and as far as I know the only way they will expand is if you insert rows/columns in them.

By the way, why are you using the last row of the sheet that is active at the start of the code for ranges on newly added sheets?

**hobbiton73** · 10-26-2013, 01:13 PM

Hi @Norie, thank you for coming back to me so quickly this and for the info on the dynamic ranges.

In respect of the 'Last Row' issue, you probably won't have had chance to see the earlier posts, but his was a suggestion put forward to me earlier on in the thread. Unless I've misunderstood, my original code didn't include this.

Many thanks and kind regards

**Norie** · 10-26-2013, 01:20 PM

What ranges do you want to create on the new sheet?

Have you considered using tables?

**hobbiton73** · 10-26-2013, 01:40 PM

Hi @Norie, yes I had thought of that. Unfortunately, although I use Excel 2013 at home, my employers currently use 2003.

It looks like I'll have to go away and think about this a little more.

Thank you very much for your time and trouble.

**JBeaucaire** · 10-26-2013, 09:21 PM

The five Dynamic named formulas I would use would be:

MPList: =OFFSET('Monthly Projects'!$B$5,,,COUNTA('Monthly Projects'!$B$5:$B$50000),)
MPResourceLOB: =OFFSET('Monthly Projects'!MPList,,1,,)
MPFFTE: =OFFSET('Monthly Projects'!MPList,,2,,)
MPAFTE: =OFFSET('Monthly Projects'!MPList,,3,,)
MPCap: =OFFSET('Monthly Projects'!MPList,,4,,)

The first, MPList, counts the values in column B from B5 down and creates a list that many rows long. All the other named ranges simply offset from the MPList range by one column to the right.

Now, VBA adds those same formulas to the sheet for you like so:

Please Login or Register  to view this content.

**hobbiton73** · 10-27-2013, 09:18 AM

Hi @JBeaucaire, thank you very much for your continued help with this and the time spent putting the script together, it is greatly appreciated.

All the best and kind regards

VBA Create Dynamic Named Range

LinkBack

Thread Tools

Rate This Thread

Display

VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Re: VBA Create Dynamic Named Range

Thread Information

Users Browsing this Thread

Similar Threads

Macro to create a Dynamic named range.

[SOLVED] Create Multiple Dynamic Named Ranges with a Macro

Create a dynamic range on named cell?

Named ranges-Should I use a dynamic named range

[SOLVED] Can you create dynamic named ranges containing array formulas?

Bookmarks

Bookmarks

Posting Permissions