Automatically Hide Row based on Cell in another sheet

[excel233]

I am using Excel 2007 and I am looking for a code to automatically hide rows in one sheet based on whether cells in another sheet have a value or are blank.

Essentially, we have Sheet1 and Sheet2. If G5 in Sheet 1 has a value, then Row 6 in Sheet2 should remain displayed. However, if G5 in Sheet1 has no value, then Row 6 in Sheet2 should hide. If G6 in Sheet1 has a value, then Row 7 in Sheet2 should remain displayed. However, if G6in Sheet1 has no value, then Row 7 in Sheet2 should hide. I need this sequence to continue till G95 in Sheet1 and Row 96 in Sheet2.

Also, is this possible to add as well: if G5:G95 in Sheet1 all have no value entered in them, then no rows hide in Sheet2. Otherwise, if one cell has value, then it defers to the above code.
Many thanks for the assistance!!!

[Mordred]

Hi excel1233, try the workbook that I created for you and let me know if I got your requirements right.

[excel233]

Yes, this seems to work. However, how can I embed this in an already created workbook? Thanks again!!

[Mordred]

Sure, just change

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

That should do it I think.

[Mordred]

I guess I should have asked before I gave you an answer but, did you look at the code provided in the workbook? If not then you'll have to create a button in your workbook, assign the Sub name to it, create a module in vbe (Alt + F11), copy and past the code from my example into your new module, confirm that the name of the Sub is the same as the name you are calling in the CommandButton1_Click() Sub, re-enter your sheet names as described above, and then you should be ready to go.

[excel233]

This works, however, I am expriencing one problem. The blank cell isn't really a blank cell, but the sum of 2 other cells, so when you click it on it, it has a formula displayed. For example, G5 has =sum(e5:f5) in it, but currently doesn't have any actual value. When running the command button this way it is somehow seeing this cell as not empty regardless of whether it has an actual value in it or not (using "" to represent blank). Is there any way we can represent this so that we are not going on whether the cell is totally empty, but rather blank representing that it has no sum value (becuase there is no input in e5 or f5)? Thanks again!!

[Mordred]

So if it is empty it will contain a "0"?

[excel233]

No, it contains a hyphen: -

[Mordred]

In my version of excel, which at the moment is 2003, when a function lik =Sum() is added, the value becomes 0 instead of having nothing in the cell. I see that you have the same version of Excel so you just have to switch out the "" with "0" as follows:

Please Login or Register  to view this content.

[Mordred]

A Hyphen? With a function in it? In that case switch out "0" with "-".

[Mordred]

Sorry, I see in your first post that you are using 2007, can you please edit your User description to say that? Even if you are using both 2003 and 2007, it is best to show them both!

[excel233]

Sorry about that! I'll change the description. Replacing the blank with a hyphen doesn't work. If I format all those cells to currency, that does cause a 0 to display as you indicated and when I do change the "" to include 0, it actually does work. But, I am wondering why it's not working with the hyphen? (I got the hyphen formating the cell and using the custom function to input the hyphen)

Also, How do you change the wording on the button from "commandbutton1" to something else?

[auhom_me]

It works. But I need this for rows of sheet a and colun of sheet B. for example value of Sheet 2s came form rows A1 to z1 . In that case what will be the VBA code

Thanks in Advancebe