Problem when using Named Range instead of simple Range

[unit285]

Here is my code: The code for Supplier1: works great. The code for Supplier2: throws an error. However, I really prefer to use Defined Names over simple ranges. There are only 3 lines that are different between Supplier1: and Supplier2: They are as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any help is greatly appreciated.

Please Login or Register  to view this content.

Product1 Defined Range:

Please Login or Register  to view this content.

PCost1 Defined Range:

Please Login or Register  to view this content.

Test Sheet

[Norie]

This makes no sense.

Please Login or Register  to view this content.

With that code VBA will be looking for a named range with the name Product11048576.

What are you actually trying to do here?

Please Login or Register  to view this content.

Are you trying to find the next empty row in the named range 'Product1'?

[unit285]

Yes. I need to find the first empty cell in Product1. I can make it work if Product1 is not empty. But if Product1 is empty i just get errors

[Norie]

If Product1 is empty then it's likely that when you try and find the next empty row you'll end up in a row above the range Product1 refers to.

Why are you using the named ranges anyway?

[unit285]

If Product1 is empty then it's likely that when you try and find the next empty row you'll end up in a row above the range Product1 refers to.

Why are you using the named ranges anyway?

Named Ranges allow me to insert columns without breaking the code for one

[Norie]

If that's the only purpose then why are they dynamic named ranges?

Couldn't you just use them for entire columns?