Cathy asks if there is a way to hide rows without the assistance of a macro. After a quick failed attempt at using conditional formatting, Bill figures out a non-macro way to hide rows in this episode of the Learn Excel from MrExcel podcast.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast Episode 1736: Hide Blank Rows Without a Macro.
Today's question sent in by Kathy.
Someone just asked whether there's a way to alter a row's height.
Actually, they want to hide the row completely without doing it in the context of a macro.
Is there a formula that would, you know, set the parameters of a row?
So, I have a mock-up, here, of Kathy's workbook. I changed the actual data, but they have this list and, for any items that are not filled in-- in this box-- they want to hide those rows.
And my initial reaction was, hey, maybe we could do this with conditional formatting.
"Alt+OD" would create a new rule, I use a formula to determine which cells to format, and then when, you know, we wanted to hide it, I would change the font size back to 1.
See, but that's kind of grayed out.
We're not allowed to choose from that list, so that is not going to work-- the conditional formatting.
Boy, this would be really easy with a Macro Alright, so, the other thing I thought was, maybe we can come out here, outside of the print range, and we could create an auto filter range.
All right, so, "Hide This Row?"-- here's the heading-- and we could build any kind of a formula here.
Now, here, my formula's actually easy-- It's going to be =ISBLANK of this cell over here.
But this formula can be as complex as you can imagine and, actually, I noticed on Kathy's workbook there was a statistic here that we never wanted to hide-- that particular row.
So, even if that was blank, we needed to show this revenue total here, or something, so you could modify that formula, so, that way, you know, if Column B wasn't blank, then it wouldn't be true, or something like that.
Alright, so we have this section out here, it's outside of the context of the print range, so, now, we see it, you know, if you wanted to, you can even put it out in Column Z, where it won't show up on the screen.
And we'll apply a filter and say, "Hide This Row"-- we wanna un-hide the truths.
Okay.
Alright, now, here's the beautiful thing about this: So, let's say that one of these items gets cleared, and we delete it-- click delete-- it doesn't disappear automatically, but, all we would have to do, is come back to the data tab and click the Reapply button.
See, now, I'm not even in the auto filter area.
I don't have to select one of those cells since there can only be one auto filter per worksheet.
It's obvious to Excel what we're trying to do.
Want to click Reapply-- that gets hidden.
Cool, right?
No macros seems to work but, you know, the big problem is, what if they had it at Add Something?
Now, all of the blanks are hidden, so, I guess you'd have to come out here to data and then clear the filter to get everything back.
To make that simpler, it might have been good to go to View, Custom Views, and create a new view called, "Show All", Click OK, and then apply the filter hiding the truths.
And, now, Custom Views will create a new view-- add-- Called "Hide Blanks".
Click OK, and then it would be a moderate of Custom Views.
We could double click on "Show All" to get them back.
We could edit something, and then Custom Views, go back to Hide Blanks, and it would hide on the fly.
So, again, not a macro method.
Boy, this would've been so much easier with a macro but, I think using the Auto filter outside of the print range Is one way to do this without a macro.
Alright, well, I want to thank Kathy, for sending that question in.
And I wanna thank you for stopping by.
We'll see you next time on another netcast from MrExcel.
Learn Excel from MrExcel Podcast Episode 1736: Hide Blank Rows Without a Macro.
Today's question sent in by Kathy.
Someone just asked whether there's a way to alter a row's height.
Actually, they want to hide the row completely without doing it in the context of a macro.
Is there a formula that would, you know, set the parameters of a row?
So, I have a mock-up, here, of Kathy's workbook. I changed the actual data, but they have this list and, for any items that are not filled in-- in this box-- they want to hide those rows.
And my initial reaction was, hey, maybe we could do this with conditional formatting.
"Alt+OD" would create a new rule, I use a formula to determine which cells to format, and then when, you know, we wanted to hide it, I would change the font size back to 1.
See, but that's kind of grayed out.
We're not allowed to choose from that list, so that is not going to work-- the conditional formatting.
Boy, this would be really easy with a Macro Alright, so, the other thing I thought was, maybe we can come out here, outside of the print range, and we could create an auto filter range.
All right, so, "Hide This Row?"-- here's the heading-- and we could build any kind of a formula here.
Now, here, my formula's actually easy-- It's going to be =ISBLANK of this cell over here.
But this formula can be as complex as you can imagine and, actually, I noticed on Kathy's workbook there was a statistic here that we never wanted to hide-- that particular row.
So, even if that was blank, we needed to show this revenue total here, or something, so you could modify that formula, so, that way, you know, if Column B wasn't blank, then it wouldn't be true, or something like that.
Alright, so we have this section out here, it's outside of the context of the print range, so, now, we see it, you know, if you wanted to, you can even put it out in Column Z, where it won't show up on the screen.
And we'll apply a filter and say, "Hide This Row"-- we wanna un-hide the truths.
Okay.
Alright, now, here's the beautiful thing about this: So, let's say that one of these items gets cleared, and we delete it-- click delete-- it doesn't disappear automatically, but, all we would have to do, is come back to the data tab and click the Reapply button.
See, now, I'm not even in the auto filter area.
I don't have to select one of those cells since there can only be one auto filter per worksheet.
It's obvious to Excel what we're trying to do.
Want to click Reapply-- that gets hidden.
Cool, right?
No macros seems to work but, you know, the big problem is, what if they had it at Add Something?
Now, all of the blanks are hidden, so, I guess you'd have to come out here to data and then clear the filter to get everything back.
To make that simpler, it might have been good to go to View, Custom Views, and create a new view called, "Show All", Click OK, and then apply the filter hiding the truths.
And, now, Custom Views will create a new view-- add-- Called "Hide Blanks".
Click OK, and then it would be a moderate of Custom Views.
We could double click on "Show All" to get them back.
We could edit something, and then Custom Views, go back to Hide Blanks, and it would hide on the fly.
So, again, not a macro method.
Boy, this would've been so much easier with a macro but, I think using the Auto filter outside of the print range Is one way to do this without a macro.
Alright, well, I want to thank Kathy, for sending that question in.
And I wanna thank you for stopping by.
We'll see you next time on another netcast from MrExcel.
