In the third episode about trying to hide blank rows, a simple pair of macros to show all and hide blanks. You won't lose the Undo feature for this workbook.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1738 - Hide Blank Rows!
I know, this is the third day, we started out in 1736, Cathy said "Hey, I want you to do this without a macro, hide anything in this box that doesn't have a value." But then 1737, yesterday, she let me use an EventHandler macro, and well, OK, there's a lot of problems with EventHandler macros.
#1, every time we change the worksheet the macro runs which clears the Undo stack, so we've lost Undo, and then this stupid thing just keeps hiding.
So I delete that value, and they hide the stuff, right, and I don't want it to hide automatically.
I want to be able to type something in, and not have to do Format, Row, Unhide.
Alright, so let's just get rid of that EventHandler macro, it makes no sense at all.
We're going to cut Ctrl+X, and just use a regular macro, that's the way to go here.
Insert Module, I mean if we're allowed to use macros, if we're allowed to use the EventHandler macro, then, you know, we should just be able use regular macros, and we don't lose the undo stack.
So HideBlanks, and inside of HideBlanks I'm going to paste the cut macro, because we can reuse that.
So we had a range called CheckRange, and I'm just going to say If cell.Value > "" Then .Hidden = FALSE, otherwise .Hidden = TRUE.
Right, that way, if anything's filled in, even like A12345, it will still work.
And let's see, to ShowAll we'll do Range("CheckRange").EntireRow.Hidden = FALSE.
Alright, so now we have these two macros, ShowAll and HideBlanks.
I don't know what the original intent was that we're not allowed to run macro, so we're going to forget that we run the macro?
Is it in the way to run the macro?
You know, you can customize the Quick Access Toolbar, and say that this is only for this particular workbook, Podcast1738, we can go to Macros, and say HideBlanks, we'll add that, ShowAll, we'll add that.
HideBlanks, we can change, let's see, choose an icon that's going to remind us of HideBlanks.
Like I don't know, the cloud, and then click OK.
Choose icon that's going to remind us of ShowAll, which is kind of like clearing the filter, of course they don't give us something like that, click OK, click OK, alright.
And then we can come along here, we can type values, we can clear values, nothing is happening automatically, the Undo stacks not getting cleared.
When we're done typing there, and we just click this HideBlanks, and it goes away.
Later when we need to add a new one, just click the ShowAll and it comes back.
So two little buttons up there that do the trick.
If we can't remember to do that, you know, I don't know, maybe if we had a little note here that says "Hey, click those icons above." Or we can just insert some Clipart or a shape, or you know, something, like the lightning bolt, lighting bolt, and say Assign Macro, and that one is for HideBlanks.
Alright, so click the lightning bolt, and it gets hidden, and then you have another little lightning bolt or something else to bring it back, and this is outside of the print range.
I don't know, I'm not sure why the original request said "Don't use a macro." And then on Tuesday we were allowed to use an EventHandler macro which causes all kinds of problems.
So let's just have two macros in here, that will do the trick, and we run them when we want to run them, that's how I would do this in real life if it was me.
So there you go, a couple little macros, ShowAll, HideBlanks, we even assign them to shortcut keys if we wanted to.
OK well, I want to thank Cathy for sending that question in two days ago, and for listening to my rant today.
I want to thank everyone else for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1738 - Hide Blank Rows!
I know, this is the third day, we started out in 1736, Cathy said "Hey, I want you to do this without a macro, hide anything in this box that doesn't have a value." But then 1737, yesterday, she let me use an EventHandler macro, and well, OK, there's a lot of problems with EventHandler macros.
#1, every time we change the worksheet the macro runs which clears the Undo stack, so we've lost Undo, and then this stupid thing just keeps hiding.
So I delete that value, and they hide the stuff, right, and I don't want it to hide automatically.
I want to be able to type something in, and not have to do Format, Row, Unhide.
Alright, so let's just get rid of that EventHandler macro, it makes no sense at all.
We're going to cut Ctrl+X, and just use a regular macro, that's the way to go here.
Insert Module, I mean if we're allowed to use macros, if we're allowed to use the EventHandler macro, then, you know, we should just be able use regular macros, and we don't lose the undo stack.
So HideBlanks, and inside of HideBlanks I'm going to paste the cut macro, because we can reuse that.
So we had a range called CheckRange, and I'm just going to say If cell.Value > "" Then .Hidden = FALSE, otherwise .Hidden = TRUE.
Right, that way, if anything's filled in, even like A12345, it will still work.
And let's see, to ShowAll we'll do Range("CheckRange").EntireRow.Hidden = FALSE.
Alright, so now we have these two macros, ShowAll and HideBlanks.
I don't know what the original intent was that we're not allowed to run macro, so we're going to forget that we run the macro?
Is it in the way to run the macro?
You know, you can customize the Quick Access Toolbar, and say that this is only for this particular workbook, Podcast1738, we can go to Macros, and say HideBlanks, we'll add that, ShowAll, we'll add that.
HideBlanks, we can change, let's see, choose an icon that's going to remind us of HideBlanks.
Like I don't know, the cloud, and then click OK.
Choose icon that's going to remind us of ShowAll, which is kind of like clearing the filter, of course they don't give us something like that, click OK, click OK, alright.
And then we can come along here, we can type values, we can clear values, nothing is happening automatically, the Undo stacks not getting cleared.
When we're done typing there, and we just click this HideBlanks, and it goes away.
Later when we need to add a new one, just click the ShowAll and it comes back.
So two little buttons up there that do the trick.
If we can't remember to do that, you know, I don't know, maybe if we had a little note here that says "Hey, click those icons above." Or we can just insert some Clipart or a shape, or you know, something, like the lightning bolt, lighting bolt, and say Assign Macro, and that one is for HideBlanks.
Alright, so click the lightning bolt, and it gets hidden, and then you have another little lightning bolt or something else to bring it back, and this is outside of the print range.
I don't know, I'm not sure why the original request said "Don't use a macro." And then on Tuesday we were allowed to use an EventHandler macro which causes all kinds of problems.
So let's just have two macros in here, that will do the trick, and we run them when we want to run them, that's how I would do this in real life if it was me.
So there you go, a couple little macros, ShowAll, HideBlanks, we even assign them to shortcut keys if we wanted to.
OK well, I want to thank Cathy for sending that question in two days ago, and for listening to my rant today.
I want to thank everyone else for stopping by, we'll see you next time for another netcast from MrExcel!
