Shift named dynamic range down

[HeyInKy]

I have a named dynamic range in the workbook, and I'm wanting VBA to select it and shift it down one row.

I keep trying to select the range using .Range("NamedRange") and then using shift xlDown, but it doesn't like it. I'm thinking maybe I need to name my range in the code itself rather than use .Range("NamedRange")??

[TMS]

A Named Range is usually there so you "know where it is" despite movements on the sheet, for example, rows and columns being inserted. A Dynamic Named Range is a variation on a theme usually depending on a count of cells to determine where the last row is.

If you want to move a Dynamic Named Range down, you need to change the cell it uses as a start cell and adjust the counter to take into account any blank cells inserted above it.

Regards, TMS

[HeyInKy]

So this is my current code (in a user form), which it doesn't like

Please Login or Register  to view this content.

[Bernie Deitrick]

Maybe?

Please Login or Register  to view this content.

[HeyInKy]

I thought your first post with:

Please Login or Register  to view this content.

was getting closer ...

The problem I see with your edited solution is I really don't want to insert a new row. My range is:

Please Login or Register  to view this content.

which covers A:H beginning in row 7 and on down, but I have d other data in columns J- W that aren't a part of this range/function.

[Bernie Deitrick]

I think, maybe:

Please Login or Register  to view this content.

[HeyInKy]

Definitely getting closer!

It's putting in rows where it shouldn't though... I tried changing the .Rows(2) to (1) and even (0) with no luck...Sample 1.xlsx.xlsm

[TMS]

Is this what you are looking for:

Please Login or Register  to view this content.

Regards, TMS

[Bernie Deitrick]

Change your name definition for CoverageList to this, to withstand insertions:

=OFFSET(INDIRECT("Coverage!$A$7"),0,0,COUNTA(Coverage!$A:$A),8)

And use this code

Please Login or Register  to view this content.

[HeyInKy]

Perfect! Thank you!!