+ Reply to Thread
Results 1 to 12 of 12

clear dynamic named range content

  1. #1
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    clear dynamic named range content

    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.
    Last edited by intex; 07-12-2014 at 08:31 AM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: clear dynamic named range content

    Try something like this:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: clear dynamic named range content

    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.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: clear dynamic named range content

    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.
    If posting code please use code tags, see here.

  5. #5
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: clear dynamic named range content

    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
    Last edited by intex; 07-12-2014 at 07:54 AM.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: clear dynamic named range content

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

    How have you defined 'print_clear'?

  7. #7
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: clear dynamic named range content

    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

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: clear dynamic named range content

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

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: clear dynamic named range content

    Try

    =OFFSET(CheckSheet!$A$11,1,0,Max(1,COUNTA(CheckSheet!$A$12:$A$161)),9)
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  10. #10
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: clear dynamic named range content

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

    Thanks Norie

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: clear dynamic named range content

    What exactly are you trying to do?

    Do you need the named range anywhere else?

  12. #12
    Forum Contributor
    Join Date
    08-08-2012
    Location
    englang
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: clear dynamic named range content

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Clear Content if cell in a range contain #N/A VBA
    By alex_shin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2014, 06:33 PM
  2. [SOLVED] Using named range to clear cell data
    By Nuffers in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2012, 07:23 PM
  3. replace each content linked together to a named range
    By dvb_24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-24-2011, 03:11 AM
  4. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  5. Clear Contents of Named Range in Excel 2007
    By Warderbrad in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2009, 03:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1