Macro to hide rows when total column is zero

[Brit]

Hi,

I have been trying to work out how to hide rows when a total column is zero (without success!) . I could use the auto filter but i want it to do it automatically so that if a total changes from zero to a value in a hidden row then it becomes unhidden straight away without having to redo the filter each time, or press any buttons.

I have a table; A9:T110, with column T being the total column. Data is entered in the same sheet but below the table in a list and the table summarises the data using a SUMIF so that it shows the total of any value that has been used more than once. Because the table has so many rows i would like to be able to only show the rows where there is a value in the total column (T). If a row is hidden because it is zero data is entered that then feeds into that row it automatically becomes unhidden. I hope that this makes sense?!?

Is this possible and can anyone help? I am assuming that some kind of Macro will be involved.

Many thanks,

[Lifeseeker]

Hi Brit,

It helps to post a dummy workbook so that we can take a look at the problem more easily.

[Brit]

Hi,

Here is a sample of the workbook. I hope this helps and thanks for looking.

[ThePower777]

try to use this code in Cost worksheet:

Please Login or Register  to view this content.

[Lifeseeker]

Ah Yes.

I was also trying to arrive at the solution, but ran into syntax errors.

Thanks to Power777 as well.

[Brit]

Thank you, but i think i must be being an idiot as i can't get seem to get it to work.

I have tried copying and pasting code in as a module in visual basic but nothing seems to happen. I am sure the answer is simple but macro are not my strong point at all. Could you please tell me what i should be doing?

[ThePower777]

you have to put this code not in separate module, but in your sheet11111111111111(Costs) Declaration because this event fires only when you change Costs worksheet.
Also make sure that macros enabled in excel.
Hope it help

[Brit]

I've got it working, HORAH!!!!!!!!!! Thank you for your help.

Your help is very much appreciated, an excellent answer.

[Brit]

Sorry, i have a problem again.

I have input code and all works fine as long as i have a value in every month. If i input a value directly into only one or two months then it does not appear in the top. I have attached workbook, please see line 192 where i have values for cost 95 but it is not unhiding the row above.

Thanks,

[Brit]

you have to put this code not in separate module, but in your sheet11111111111111(Costs) Declaration because this event fires only when you change Costs worksheet.
Also make sure that macros enabled in excel.
Hope it help

Thanks.

Sorry, i have a problem again.

I have input code and all works fine as long as i have a value in every month. If i input a value directly into only one or two months then it does not appear in the top. I have attached workbook, please see line 192 where i have values for cost 95 but it is not unhiding the row above.

Thanks,

[Lifeseeker]

Hi Brit,

I have fixed the problem.

I think the problem was that the loop was set to end at column 19 when it should have been 20.

Now if you have values only in a few columns on for 192, it will appear at the top.

Lifeseeker

replace existing code with this one:

Please Login or Register  to view this content.

[Brit]

Hi Brit,

I have fixed the problem.

I think the problem was that the loop was set to end at column 19 when it should have been 20.

Now if you have values only in a few columns on for 192, it will appear at the top.

Lifeseeker

replace existing code with this one:

Please Login or Register  to view this content.

Amazing. Thank you for all your help, very much appreciated.

[Lifeseeker]

thanks to Power777 as well.

I also learned something new!