+ Reply to Thread
Results 1 to 18 of 18

Hide sheet if empty cell

  1. #1
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Hide sheet if empty cell

    Good evening, I need a code that hides the sheet if a cell is empty.
    In the file attached to the Dati Sheet from G6: G13 I have dates
    each cell corresponds to a single sheet as indicated (but which in the original are not written) in H6: H13.

    When a cell in the G range is empty, I would like the corresponding sheet to be hidden.

    Empty cells are not random but always in order.

    Example if the G10 is empty, the others are also empty up to G13.
    If the G7 is empty, the others are also empty up to G13.

    I hope it is clear.
    Thank you for your help
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Hide sheet if empty cell

    Try this code
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Thanks for your help
    two things
    1 - hide or uncover the sheets automatically without launching the macro then with the change event of the sheet I think.
    2 - insert the dates again if you want to discover the hidden sheets.
    Thank you

  4. #4
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    I apologize, I put the code in the form on the Dati sheet with:
    Private Sub Worksheet_Change (ByVal Target As Range)

    and it seems that I do what I asked for.
    I'll do other tests and let you know

  5. #5
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    so the problem is that the dates in the G range come out of a formula.

    = if ('1a settimana'! C12 = "", "", ('1a settimana'! C12))

    So when the cell is empty there is always the formula and it does not work.
    Sorry but I did not think the formulas influenced the code.
    If you can fix thanks again.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Hide sheet if empty cell

    Another option
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Thank you too Fluff13
    but as I said above I have the same problem
    If the cell is empty it contains a formula and does not work.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Hide sheet if empty cell

    It works for me with the formula you've shown
    Attached Files Attached Files

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide sheet if empty cell

    Fluff's is better but here's mine:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  10. #10
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Here I was thinking of simplifying the file to solve but I see there are problems.

    @xladept
    * your code hides all the sheets instead it has to hide only the sheets that have the corresponding empty cell G .

    I send a new file much closer to the original.
    In practice, I enter the start date in cell B8 and end date in cell F8 of the Home sheet.
    At this point the column G6: G13 Dati sheet is filled in with the dates.

    I would like as in the case of the attached file the cells G11: G13 are empty and therefore the relative sheets 6-7-8a Settimana hide them.

    If you fill with the dates you will have to find out

    If possible automatically
    Thanks I hope it's clearer.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Hide sheet if empty cell

    The problem is that you have errors in the formulae.
    Try
    Please Login or Register  to view this content.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide sheet if empty cell

    Correction:

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Then with the first code From Yasser inserted in the Home sheet
    with:
    Private Sub Worksheet_Change (ByVal Target As Range)


    I solve the problem before I inserted it in the Dati sheet.

    @Xladept
    the code keeps hiding all the sheets.

    Thank you all

  14. #14
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Sorry Fluff13
    even your macro now works
    Thank you

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide sheet if empty cell

    A problem of Case:

    Please Login or Register  to view this content.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide sheet if empty cell

    Hi Berna,

    Thanks for the reps!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Hide sheet if empty cell

    Glad we could help & thanks for the feedback

  18. #18
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Hide sheet if empty cell

    Ok xladept
    your macro now works too.
    Thanks again

+ 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. Hide rows if the cell is empty
    By juliettelam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-24-2014, 12:38 PM
  2. [SOLVED] Trouble copying 1st empty cell from one sheet to 1st empty cell in 2nd sheet.
    By BobW1952 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-29-2014, 02:56 PM
  3. Hide Rows on one Sheet based on cells in another sheet being empty
    By KDF in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2013, 01:48 PM
  4. Hide row if cell is empty
    By JamesT1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-25-2012, 10:26 AM
  5. Hide Row If Cell is Empty
    By henapen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2011, 07:20 AM
  6. Hide row if cell is empty
    By jturman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2010, 06:29 PM
  7. Hide row if cell is empty
    By Qaspec in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2005, 06:05 PM

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