A simple hide rows VBA macro that requires no VBA knowledge. The macro allows you to hide unused rows e.g. 0’s in income statements.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 07:10
- - Skill Level: 6
- - What's my skill level
Hide rows VBA (with only a simple macro)
Here we have a spreadsheet with an Income Statement. What you’ll see is the Income Statement is very comprehensive, but in this situation there are a lot of zeros. The problem is, if we print this out we have got lots of rows with zeros. So what we want to do is create some sort of a mechanism that we can click a button and hide anything with zeros in using a Hide rows VBA function in Excel.
What I would do is the following. I’d find a column somewhere over here and we are going to build a simple formula – the IF formula. And what we are going to check for, in this case, is is that cell bigger than zero? If it is bigger than zero, for now I am just going to put a 1, if it’s not I am going to put a zero. When I say OK I get that. For now, I am just going to copy it down to the very bottom. Now we can quickly go, highlight this, and go switch the filter on and at this stage you can check it.
So here we have got our calculations and I’m going to put my filter on and only show the ones. And you can see it has kinda done the job, but there are some problems with it. So let’s look at how you address these problems. I am just going to take that off. The first problem we had, was when we hid the rows, all of these disappeared and this is something that I always want to appear. So whereas this is a formula, and it is always checking if there is a number there, over here I am just going to type in ones. And just to make sure I know that that was typed in, I normally just go put a colour on it so that I am aware.
The second problem is that, if you look here you’ll see there is amounts and there is a sub-total and if we go down, you’ll notice that there are some situations, where I have got no information in the underlying – the sub-total is zero and our formula result in that being hidden. If that is what you want, great, but in my case I want to see these sub-totals even if they are zero. So again, I am going to go and just wherever there is a sub-total, I am going to overwrite with a one. And I must remember just to put the colour in so we know what is happening.
So now we have done all of them. You’ll notice here as well- it is giving me a zero because the formula is saying that is a blank – I actually want this row to be shown, so I’m actually going to put one there as well. Now we can go to the top, and let’s try it manually first – so I’m going to say I only want to see the ones – OK – and you can see it comes through – it seems to be making sense. Let’s just go back to see everything. If you remember here we have got electricity previous letter shown, I’m going to change that to a zero and put 100 here for example. Let’s hide everything again – so I only want to see the ones. So you’ll see electricity is gone and now this new number appears. So I am just going to switch them all on.
Now let’s make this more user friendly, by making a button that you can click to open and close without having to do this. So the way we are going to do that – we are going to go to view and I’m going to go to record a macro – I’ll give it a name – so I’m going to say – HIDE ROWS – store it in this workbook. When I say OK, I am now recording my steps. All I’m going to do is go to the cell, click here and switch on that we only see the ones. And I’m going to say OK. Now I’m going to stop the macro recorder.
Let’s record another macro now to UNHIDE it – so I’m going to go here, record macro and I’m going to call it UNHIDE ROWS – store it in this workbook. When I click OK. And all I’m going to do here is go to the filter, say select all and say Ok and again I am going to stop the recorder.
Once that’s done, let’s see how it works. First what I’ll do just to make it a bit neater – I am going to hide that column, and let’s now go via the macro – I am going to say View Macros – in this workbook I am going to say hide rows when I run it – can you see it did it automatically – unhide rows – run – it does it automatically.
Now let’s put buttons on just so we don’t have to continually refer to the ribbon. So what I’m going to do is insert some shapes that look like buttons – we’ll just do that one. I am just going to right click and say EDIT TEXT and write the word HIDE. I’m just going to copy that button, paste it and change the text here to read UNHIDE. If I now right click on a button and say assign macro – I can say hide rows – right click on this button and say assign macros and say unhide rows and now we have our buttons set up. Let’s see how they work now – I am going to hide and you’ll see everything is hidden, I’m going to unhide – everything is unhidden. And if I make changes – let’s just let me change those to zeros, when I say hide, you’ll see they disappear – we can unhide and we’ll see all the lines again.
We would recommend that you build some ERROR checks in just to make sure, for example – if these numbers are coming from another sheet that is always visible, there may be a number that is currently hidden, so perhaps one of those zeros turned to a number – so it would be useful to have an error check that warns you that some certain numbers are hidden and you need to pretty much unhide and rehide again.