+ Reply to Thread
Results 1 to 9 of 9

Hide empty cells

  1. #1
    Forum Contributor
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    130

    Hide empty cells

    So I stole this from another excel sheet I had for a similar activity. What I would from this code is, to start from the bottom row with text (not just a formula, a formula is written to return " ") any row that has returned " " above the bottom row that had text in it, in column A, hide that row.
    My code is posted below, and my excel sheet is attached. It is the "Mine" sheet that I'm trying to work with

    Thank you for any help!

    Edit: I guess my issue with the current code is that it only works with the Range A4:A2000. I would like it to work with the entire sheet staring from the last row that returned a value, then hide all cells (up to row 4) that returned " "

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PNick; 01-13-2018 at 11:19 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Hide empty cells

    You can't hide a cell, you can only hide a row or a column. Can you clarify what you need?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    130

    Re: Hide empty cells

    opps thanks yeah hide row

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Hide empty cells

    Quote Originally Posted by PNick View Post
    I would like it to work with the entire sheet star[t]ing from the last row that returned a value, then hide all cells[rows] (up to row 4) that returned " "
    Focusing on that part of your description, the minimal change would be:

    Please Login or Register  to view this content.
    I did not download your file and test this, although I compiled it.

    Another more streamlined version would be:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    130

    Re: Hide empty cells

    Thanks for the attempt, those codes both hide the rows which have formulas that return " " prior to the first text

    I think you need to open my sheet to better understand my request.

    Basically I have a sheet named "mine" which draws values from another sheet using formula
    Please Login or Register  to view this content.
    What I would like is, a macro that searched for the first time (from bottom to top) that the above formula returned a value other then " ". once it found that row, every time above that row it hid the cell where " " was returned from the above formula.

  6. #6
    Forum Contributor
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    130

    Re: Hide empty cells

    Still seeking assistance with this problem...

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Hide empty cells

    I see the problem. Opening your file didn't help but your second explanation did clarify things. The expression in the original code I provided that finds the last row that has any content, which may be a formula that returns "". You want to find the last row with a formula that returns some text, and leave the subsequent rows visible.

    Here is revised code that does this:
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    130

    Re: Hide empty cells

    That's the one! thank you very much!

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Hide empty cells

    You're welcome! Thanks for the rep, and thanks for marking your thread Solved!

+ 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. HELP: How to hide empty or blank columns/cells?
    By nez329 in forum Excel General
    Replies: 10
    Last Post: 11-07-2014, 11:27 AM
  2. Hide empty cells in this code
    By JanF in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-14-2014, 04:12 AM
  3. Unable to hide empty cells from a chart
    By katieshields in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 04-01-2013, 06:33 AM
  4. [SOLVED] Excel 2007 : Hide Empty Cells when Plotting a Graph
    By HangMan in forum Excel General
    Replies: 13
    Last Post: 06-22-2012, 04:48 AM
  5. Excel 2007 : How to Hide rows if data cells are empty?
    By gene070752 in forum Excel General
    Replies: 0
    Last Post: 12-04-2011, 05:00 PM
  6. Macro to hide columns with empty cells.
    By bailey73 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-31-2011, 11:55 PM
  7. Macro to hide empty cells
    By sivdin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2009, 02:45 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