Resize all named ranges on a sheet with LastRow

[zsnemeth]

Hi All,

I would like to resize all the named ranges on a worksheet, where I have about 30 columns next to each other, so the LastRow of them is the same.
This is actually a table, where sometimes a new row will be filled in completely.
That is why I need to enlarge all the named ranges (all names belong to 1-1 column) by 1 row, practically with the "new" LastRow of the table.
The first row of all ranges is always the row nr. 5.

Example:
MyRng_A = "$A$5:$A$" & LastRow
MyRng_B = "$B$5:$B$" & LastRow
MyRng_C = "$C$5:$C$" & LastRow

These examples are simple, but my range names are longer and more difficult, that is why I search for a smarter solution.

So, do I have to type all those commands into the code, or is there an option like "For Each" or so?
I do not know how to do this.

Could anyone please help me?

Thank you very much for your help!

Cheers,
Zs

 

[AlphaFrog]

Have you considered creating dynamic named ranges so they autosize when you add a new row?

 

[zsnemeth]

Have you considered creating dynamic named ranges so they autosize when you add a new row?

No, I did not.
I am not sure, that I know that.
I'll search for it. Thanks!

 

[zsnemeth]

Have you considered creating dynamic named ranges so they autosize when you add a new row?

Just one Question, if I may:

What is the difference between a dynamic range (as written on the linked page), and if I would set my Range to the entire column, started at the 5th row?

Thanks!

 

[AlphaFrog]

A dynamic range would end at your current last used row.

A range set to the entire column would end at the very last row at the bottom of the sheet.

The two ranges may or may not work the same depending on how you use the named range.

Also, this is a link to a tutorial:
dynamic named ranges