Copy/insert and keep range membership

Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Thanks Venkat. This isn't quite what I'm looking for.
I have data in column A, rows 1 through 5.

Cells A2, A3, A4 are a range called myrange.

When the active row is 3, and I do a VBA copy/insert, the new row is in the range myrange bringing the total rows in the range to 4.

When the active row is 2 (in other words, at the top of the range myrange), the VBA copy/insert inserts a new row, BUT, the new row is not in the range myrange (myrange remains 3 rows big). The xlup and xldown don't make a difference.

I want the new, inserted row to be included in any range that is intersected by activerow (where the cursor is when I do the copy/insert).

Thanks much.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think for that you have to change the definition of your named range

pseudo code would be some thing like this

if active cell row is the first row of the range then

a. insert row
b. change the name range definition to start from active cell row

end if