Hide rows in worksheet with dependency on variable in cel

Hi, Judith,

this code will only work for a manual entry into A1 of Sheet1. Right click on the Worksheet tab, choose View Code and paste the code into the code window:

Please Login or Register  to view this content.

Code will work only if one cell is edited, will check for A1 to be the cell to be edited, and then decide whether or not to hide the rows 41 to 45.

The command you found will only work for worksheets.not for rows.

If you want to have more cells like A1 to C1 you must make sure if theses cells should be looked upon indepently from each other (change Range("A1") to Range("A1:C1")) or should operate together (all must show either Yes or No).

Ciao,
Holger

ciao Holger,

Thanks for answering:

I tried... but I cannot get it working.
first the value in A1 is not a manual input but a result of a formula. BUt if set that beside and put manually No in cel A1, it still does not hide the rows....
The name of the sheet is '4' I don't know if that is info you need?

Wat does the 'target.count' do?

I was thinking more like of an if function like below:
if cel A1 = "No" then Rows("41:45").Hidden

I think I can work around that A1 should be a manual input. By copy cel a1 and past it as a value in A2 en then referring the formula to A2?

Hi, Judith,

no problem:

Please Login or Register  to view this content.

This code evaluates if the Range A1 shows Yes (delivers False, rows will be visible) or No (delivers True, Rows will be hidden).

Target.Count counts the number of cells which are being changed - you may mark cells prior to entering and close instead of just Enter with CTRL+ENTER to fill into mulitple cells.

Maybe have a look at the workbook attached, first worksheet for manual entry, second for change of formula.

Ciao,
Holger

Hi, mehmetcik,

maybe think about

Please Login or Register  to view this content.

being a bit of overkill - Rows equals the entire row AFAIK.

Ciao,
Holger

Hi, Judith,

UCase (Upper Case) changes Yes to YES - please change the code to read

Please Login or Register  to view this content.

Ciao,
Holger

Thanks I have seen it working now once....

after I make it YES in the code it worked

I tried again:
and then excel stopped working......
and when it does not stop working it gves a Runtime error:

"Method "hidden' of aobject "Range 'failed

Hi, JuŽdith,

to be honest: I would not rely on the Worksheet_Calculate-Event but take the Worksheet_Change-Event for Range C20 and C25 (check for the value in C28 from there) instead as these cells directly influence the formula in C26 as well as C28 and may be taken to trigger the macro.

Ciao,
Holger

Hi, Judith,

sorry but I was wrong about the cells which need to be monitored. Worksheet_Change-Event may look like this:

Please Login or Register  to view this content.

You should use only one of these events, not both for the very same sheet.

Ciao,
Holger