In episode 1736, the goal was to hide blank rows without using a macro. It turns out the real requirement is to hide blank rows without having to remember to run a macro. Thus, an Event Handler macro that would automatically run in the background is acceptable. In today's episode, learn how to set up an event handler macro that silently runs every time the worksheet is changed. This macro will hide rows that are non-numeric in column C.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1737 - Hide Blank Rows without Having to Remember to Run a Macro!
Hey welcome back, this is Deja vu from 1736, turns out that Cathy wasn't really opposed to macro, she was just opposed to having to remember to run the macro, so it wanted to be automatic. And I offered something called an EventHandler macro, that would hide the blank rows in this box. So anything here in this box is going to get hidden automatically.
Now a couple of things, I noticed that Cathy's file is an xlsx, so we're going to have to do File, Save As, and save this as Macro Enabled, otherwise the macro won't be allowed to run, so I'll have to do that. And then you also have to check your tools, macro security, which I do Alt+T M S, there is of course a better way to do that, through File, Options, and Trust Center, yada yada yada. Make sure that you're not here at Disable all macros without notification, you want to go to the second one, where they notify you that they're going to disable macros, that allows you to turn them back on. So we run it, do those two things. The other huge problem that we have with macro, a macro is going to be hardcoded to look at rows 16-24, and as someone deletes the row here, or inserts a row here, the macro's not going to work anymore so stealthily. And before I do this, I selected that range, and named that range, let's call it CheckRange or something like that. Alright, so now there's a named range, and I know, in the macro we want to check all the sales in checked range.
Alright so, here we go, we're in Podcast1737.xlsm, I'll press Alt+F11, that will get us to VBA.
If you can't see the Project Explorer, we do View, Project Explorer, or Ctrl+R. Find the workbook, Podcast1737, and we're going to go to the sheet that has the data, and double click. From the top left drop-down choose Worksheet, it's the only thing to choose.
They choose SelectionChange, I'm going to change it to be- Oh boy, we could do Calculate, we could do Change, let's do Change, that way every time someone changes the worksheet, this macro will run.
Alright, and what are we going to do, we're going to say For Each cell In Range, was it called CheckRange, it has to correspond to the name that you just created back in the sheet, Next Cell. And we're going to say, let's see, we want to check to see if that is filled in, and in this case it was numeric.
So we can say If Application.WorksheetFunction.IsNumber(cell) , they don't have IsBlank in here, it's a little annoying, IsNumber. Then , so if there's a number there, we want to keep that row visible, so we say cell.EntireRow.Hidden = FALSE . Otherwise, if there's not a number there, then cell.EntireRow.Hidden = TRUE , and End If. So that's the code right there. Alright, so WorksheetChange, yep, we're going to do Debug Compile, make sure I didn't spell anything wrong. Alright, go back here to Excel. And the way that this works is, you see that it didn't automatically hide things because I haven't changed the worksheet.
So I just have to come to any blank cell, type the number 1, and- Aah, bang, right there, it's hidden.
Alright, now we're going to have the exact same problem that we had yesterday in episode 1736. What if I need to fill in something?
Ah, we're going to use Go To. Alright, so Ctrl+G or F5 will display the Go To dialog box, and I can go to that hidden cell. So in this case C19 is where a blank is, so I'll go to C19. I've actually selected C19 although I can't see it, and I can type the new number, 123456. When I press Enter, the macro runs again, and it unhides. Isn't that a cool, cool trick? Alright, so it's kind of a hassle to get in there and unhide, I guess you can always do Alt O R U, Format Row Unhide, and then type the new number and press Enter.
And it'll go through and unhide the unused ones, although that, frankly, would annoy the heck out of me. So this tiny little EventHandler macro, it's not running in a module, it's actually on the sheet, WorksheetChange will go through, and hide the cells that are not currently filled in with a number.
Well hey, I want that Cathy for sending that question in, and I want to thank Cathy for allowing me to use some VBA as long it was easy as (unclear), I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1737 - Hide Blank Rows without Having to Remember to Run a Macro!
Hey welcome back, this is Deja vu from 1736, turns out that Cathy wasn't really opposed to macro, she was just opposed to having to remember to run the macro, so it wanted to be automatic. And I offered something called an EventHandler macro, that would hide the blank rows in this box. So anything here in this box is going to get hidden automatically.
Now a couple of things, I noticed that Cathy's file is an xlsx, so we're going to have to do File, Save As, and save this as Macro Enabled, otherwise the macro won't be allowed to run, so I'll have to do that. And then you also have to check your tools, macro security, which I do Alt+T M S, there is of course a better way to do that, through File, Options, and Trust Center, yada yada yada. Make sure that you're not here at Disable all macros without notification, you want to go to the second one, where they notify you that they're going to disable macros, that allows you to turn them back on. So we run it, do those two things. The other huge problem that we have with macro, a macro is going to be hardcoded to look at rows 16-24, and as someone deletes the row here, or inserts a row here, the macro's not going to work anymore so stealthily. And before I do this, I selected that range, and named that range, let's call it CheckRange or something like that. Alright, so now there's a named range, and I know, in the macro we want to check all the sales in checked range.
Alright so, here we go, we're in Podcast1737.xlsm, I'll press Alt+F11, that will get us to VBA.
If you can't see the Project Explorer, we do View, Project Explorer, or Ctrl+R. Find the workbook, Podcast1737, and we're going to go to the sheet that has the data, and double click. From the top left drop-down choose Worksheet, it's the only thing to choose.
They choose SelectionChange, I'm going to change it to be- Oh boy, we could do Calculate, we could do Change, let's do Change, that way every time someone changes the worksheet, this macro will run.
Alright, and what are we going to do, we're going to say For Each cell In Range, was it called CheckRange, it has to correspond to the name that you just created back in the sheet, Next Cell. And we're going to say, let's see, we want to check to see if that is filled in, and in this case it was numeric.
So we can say If Application.WorksheetFunction.IsNumber(cell) , they don't have IsBlank in here, it's a little annoying, IsNumber. Then , so if there's a number there, we want to keep that row visible, so we say cell.EntireRow.Hidden = FALSE . Otherwise, if there's not a number there, then cell.EntireRow.Hidden = TRUE , and End If. So that's the code right there. Alright, so WorksheetChange, yep, we're going to do Debug Compile, make sure I didn't spell anything wrong. Alright, go back here to Excel. And the way that this works is, you see that it didn't automatically hide things because I haven't changed the worksheet.
So I just have to come to any blank cell, type the number 1, and- Aah, bang, right there, it's hidden.
Alright, now we're going to have the exact same problem that we had yesterday in episode 1736. What if I need to fill in something?
Ah, we're going to use Go To. Alright, so Ctrl+G or F5 will display the Go To dialog box, and I can go to that hidden cell. So in this case C19 is where a blank is, so I'll go to C19. I've actually selected C19 although I can't see it, and I can type the new number, 123456. When I press Enter, the macro runs again, and it unhides. Isn't that a cool, cool trick? Alright, so it's kind of a hassle to get in there and unhide, I guess you can always do Alt O R U, Format Row Unhide, and then type the new number and press Enter.
And it'll go through and unhide the unused ones, although that, frankly, would annoy the heck out of me. So this tiny little EventHandler macro, it's not running in a module, it's actually on the sheet, WorksheetChange will go through, and hide the cells that are not currently filled in with a number.
Well hey, I want that Cathy for sending that question in, and I want to thank Cathy for allowing me to use some VBA as long it was easy as (unclear), I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
