Hide/unhide rows with zero values

**sujittalukder** · 06-19-2007, 01:12 AM

Need a macro which will hide rows having 0 values. On running once it will hide those rows having 0 values and on pressing second time it will unhide those hidden rows and so on.
Thanks for any reply.

**rylo** · 06-19-2007, 01:20 AM

Hi

What column(s) are you checking for the 0 values?

rylo

**sujittalukder** · 06-19-2007, 02:01 AM

i need the same for column C , D, and E

**rylo** · 06-19-2007, 02:22 AM

Hi

try

Please Login or Register  to view this content.

rylo

**sujittalukder** · 06-19-2007, 02:34 AM

Thanks for the code But i want to hide the rows and not to delete the rows. As I'll assign the macro to a button, so I want that on cliking once the rows with zero values will hide and on second click it will unhide all rows, on third click again hide zero value rows and on 4th click, unhide all rows and so on. Could the code be modified in this manner? thanks

**avveerkar** · 06-19-2007, 03:57 AM

Originally Posted by sujittalukder

Thanks for the code But i want to hide the rows and not to delete the rows. As I'll assign the macro to a button, so I want that on cliking once the rows with zero values will hide and on second click it will unhide all rows, on third click again hide zero value rows and on 4th click, unhide all rows and so on. Could the code be modified in this manner? thanks

Please Login or Register  to view this content.

While unhiding it will unhide all the rows on the sheet and not only those were hidden by the macro

A V Veerkar

**sujittalukder** · 06-19-2007, 04:15 AM

Dear AV, thanks for the code but when i am running the code it is actually hiding all the rows and not only the rows with zero value. for unhiding, it is running well.

**avveerkar** · 06-19-2007, 06:01 AM

Originally Posted by sujittalukder

Dear AV, thanks for the code but when i am running the code it is actually hiding all the rows and not only the rows with zero value. for unhiding, it is running well.

The code will hide the row if there is a "0" in col C OR Col D OR Col E. I don't think that it will hide all the rows. Or do you want the condition that only when all the three columns have "0" the row should hide? In that case instead of "Or" you will have to use "And" in the if condition.

Also if any of cells in columns C, D and E are empty the row will get hidden - the code looks for "0" and empty cell. If you do not want empty-cell rows to be hidden then modify to

Please Login or Register  to view this content.

A V Veerkar

**sujittalukder** · 06-19-2007, 06:58 AM

No dear I want the OR contion macro ie if any of the column contain 0 than hide that row. But your previous code is actually hiding all the rows

**avveerkar** · 06-19-2007, 08:40 AM

Originally Posted by sujittalukder

No dear I want the OR contion macro ie if any of the column contain 0 than hide that row. But your previous code is actually hiding all the rows

My previous code will hide rows with either "0" in the cell or if it is empty. The next will not hide rows with empty cells. If it is still not working, I am a little perplexed. Can you zip the workbook and attach to your post?

A V Veerkar

**sujittalukder** · 06-19-2007, 09:50 AM

Yeah i am attaching a sample wb for your ready testing.

**avveerkar** · 06-19-2007, 10:45 AM

I saw your worksheet. I created a commondbutton on the sheet and assigned the macro to it. It seems to work ok.

Also I saw on your "statement" worksheet you have written that you want this hide-macro to run automatically when C1 changes. I have added that also. ( How do you want to unhide now? ).

Rows with "0" in either col c or d or e will be hidden by clicking the button or by changing C1. Button will also unhide.

Your workbook is attached

A V Veerkar

**VBA Noob** · 06-19-2007, 12:43 PM

Is this a cross Post ??

http://www.vbaexpress.com/forum/showthread.php?t=13428

VBA Noob

**sujittalukder** · 06-20-2007, 01:05 AM

Dear avveerkar,

tested your code and found OK but one problem which you have also mentioned-
On changing Cell C1 it is hiding zero rows but not unhiding non zero rows on changing C1 again.

That is to say, if you select "B" in C1, it is hiding zero rows automatically but if you change C1 to "C" it hides zero rows but dont unhide non zero rows. Same case if you change C1 to "A" and so on.

Can this be fixed?

**sujittalukder** · 06-20-2007, 01:22 AM

Dear VBA Noob, this is not a cross post and also i have provided a link to that site if that member's requirement matches with this than he could use this one

**avveerkar** · 06-20-2007, 05:55 AM

Originally Posted by sujittalukder

Dear avveerkar,

tested your code and found OK but one problem which you have also mentioned-
On changing Cell C1 it is hiding zero rows but not unhiding non zero rows on changing C1 again.

That is to say, if you select "B" in C1, it is hiding zero rows automatically but if you change C1 to "C" it hides zero rows but dont unhide non zero rows. Same case if you change C1 to "A" and so on.

Can this be fixed?

I have added one line to take care of the problem. Replace the sub with

Please Login or Register  to view this content.

**avveerkar** · 06-20-2007, 05:57 AM

Originally Posted by sujittalukder

Dear avveerkar,

tested your code and found OK but one problem which you have also mentioned-
On changing Cell C1 it is hiding zero rows but not unhiding non zero rows on changing C1 again.

That is to say, if you select "B" in C1, it is hiding zero rows automatically but if you change C1 to "C" it hides zero rows but dont unhide non zero rows. Same case if you change C1 to "A" and so on.

Can this be fixed?

I have added one line to take care of the problem. Replace the sub with

Please Login or Register  to view this content.

A V Veerkar

**sujittalukder** · 06-20-2007, 06:23 AM

Thats fantastic! this is what i was looking for. Many Many Thanks.

One thing i have a code which prints individual records continuously on separate pages But I am unable to combine these two macros. Can you help me in this matter?

See attached file.

Thus,my requirement is that the code will print all records separately on each page but will not print rows with zero value.

**lok-kok** · 06-20-2007, 11:25 PM

Originally Posted by avveerkar

I saw your worksheet. I created a commondbutton on the sheet and assigned the macro to it. It seems to work ok.

Also I saw on your "statement" worksheet you have written that you want this hide-macro to run automatically when C1 changes. I have added that also. ( How do you want to unhide now? ).

Rows with "0" in either col c or d or e will be hidden by clicking the button or by changing C1. Button will also unhide.

Your workbook is attached

A V Veerkar

Thanks you all for this macro. I'm searching it. Thank you

**sujittalukder** · 06-21-2007, 05:08 AM

Any help please!!!!

**iodine4444** · 06-21-2007, 12:26 PM

i have a question. i'm a total newbie so i'm trying to use this code but i do not understand how to apply this code to my worksheet. i downloaded the latest ZeroRowshide.xls file with the commandbutton and it works great. however when i try to copy that code over it's not working.

can you give me any advice as to what i might be doing wrong?

basically what i've tried is:

1)copying/pasting code in VB editor into the Microsoft Excel Objects folder under in the corresponding sheet.
2)copying/pasting code into a new module, then creating a button from forms toolbar and assigning the macro to this button.

any help would be great!

**iodine4444** · 06-21-2007, 12:54 PM

actually, nm. it seemed to work if i create the button first then clicked on NEW when trying to assign the macro.

Can someone give me an example if we needed to include the "if blank and/or ZERO for col C, if blank and/or ZERO for col D, etc...." but we wanted to do it for more than just the 3 C,D,E columns, how might the code change?

Thanks

**avveerkar** · 06-21-2007, 11:42 PM

Originally Posted by sujittalukder

Any help please!!!!

Sorry Sujittalukder,

I was away for two days and could not access the forum during these days.
Have made necessary changes. I have shifted the button to "data" sheet. Please go through the comments I have put on the "Data" sheet.

A V Veerkar

**sujittalukder** · 06-22-2007, 12:39 AM

Dear avveerkar,

The help that you have provided cannot be expressed in words. You have saved me from a big big tedious task. MAny MAny thanks.

One more thing that is delighting me is that this post has been useful for some other menbers too from this forum and from other forum also.

For me,this matter is now solved.

Again, THANKS A TON

**avveerkar** · 06-22-2007, 02:54 AM

Originally Posted by sujittalukder

Dear avveerkar,

The help that you have provided cannot be expressed in words. You have saved me from a big big tedious task. MAny MAny thanks.

One more thing that is delighting me is that this post has been useful for some other menbers too from this forum and from other forum also.

For me,this matter is now solved.

Again, THANKS A TON

I am happy that I could be of some help.

A V Veerkar