asked on # Dynamic named range changes with insert

I have a dynamic named range defined like this:

=Inmatning!$A$3:INDEX(Inmatning!$A:$A;MATCH(BigText;Inmatning!$A:$A))

My problem is when I want to insert a row in the range with this code

Rows("3:3").Insert Shift:=xlDown

the definition of the named range changes to

=Inmatning!$A$4:INDEX(Inmatning!$A:$A;MATCH(BigText;Inmatning!$A:$A))

I need the named range definition to NOT change. How can I solve this?

Thanks,

/peter

=Inmatning!$A$3:INDEX(Inma

My problem is when I want to insert a row in the range with this code

Rows("3:3").Insert Shift:=xlDown

the definition of the named range changes to

=Inmatning!$A$4:INDEX(Inma

I need the named range definition to NOT change. How can I solve this?

Thanks,

/peter

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Just a remark on your MATCH function: You do know that -since you omitted the third argument- the match does not necessarily return exact matches?

@JKP,

in this case, the exact match is not required. The purpose of the last Index/Match is to return the last populated cell, hence BigText. Omitting the third argument will default it to 1, which is exactly what the formula should be.

I agree, though, that it's always better to state the argument than to omit it.

cheers, teylyn

in this case, the exact match is not required. The purpose of the last Index/Match is to return the last populated cell, hence BigText. Omitting the third argument will default it to 1, which is exactly what the formula should be.

I agree, though, that it's always better to state the argument than to omit it.

cheers, teylyn

jkpieterse:

I'm using this definition to find the last used row. BigText is =REPT("ö";50)

I'm using this definition to find the last used row. BigText is =REPT("ö";50)

Thanks, that solved it.

/peter

/peter