clear dynamic named range content

[intex]

Hi guys,

I have a dynamic name range called "print_clear".
How to say in vba if "print_clear" contains data clearcontents, but if "print_clear" is empty carry on with the rest of my code.
Thanks in advance
Please see below my full code i have highlighted in red were i'm stuck. :

Please Login or Register  to view this content.

[Ron Coderre]

Try something like this:

Please Login or Register  to view this content.

Is that something you can work with?

[intex]

Hi Ron,
I have added this line but it still comes as debug. Maybe you have some other ideas?

Please Login or Register  to view this content.

[Norie]

What's the error?

By the way, why not just clear the range without checking if it has data? The outcome will be the same, the range will be empty.

[intex]

Hi Norie,

the error " '1004' Method 'Range' ofobject'_Global' failed"
I need to check it because this button sometimes can be pressed even there is no data to print(and if it is a case then it shows debug as well).
Maybe if there is no data in this range you can add msgbox which will say "No data to print" and then exit the sub.

Thanks for your help

[Norie]

That sounds like there's a problem with the named range, not the code.

How have you defined 'print_clear'?

[intex]

This is the formula in the named range.
=OFFSET(CheckSheet!$A$11,1,0,COUNTA(CheckSheet!$A$12:$A$161),9)

It shows this error only when the range is empty

[Norie]

If there is no data in the range then that formula will fail.

[MarvinP]

Try

=OFFSET(CheckSheet!$A$11,1,0,Max(1,COUNTA(CheckSheet!$A$12:$A$161)),9)

[intex]

Maybe you can help me with that, how can i find the workaround on this issue?

Thanks Norie

[Norie]

What exactly are you trying to do?

Do you need the named range anywhere else?

[intex]

Hi MarvinP,

it works like a charm i really appreciate your help, i also would like to say thank you to Norie and Ron for help.