Using Value in Cell to hide/unhide rows

Hi to all,

I'm very new to excel and would like to know if there are any options for me to hide 2 rows(rows 1 and 2) based on specific values in a cell( say the char 'X'). The cell is located in row 3 (in cell A3)that would not be hidden. I was hoping if i can use the VBE editor to create a macro to do so.

Thanks in advance for any help rendered.

Hi

You need to use a Worksheet_Change macro. This fires every time a cell on the sheet is changed.

Open VBE, double click the worksheet you are interested in and use paste in the following code:

Please Login or Register  to view this content.

It checks which cell has changed and, if it is the one you are interested in, it calls your existing Sub.

Cheers, Rob.

Hi royUK, thanks for the tip. Sorry as I was not sure on how to put it in the format, but your linked proved helpful. I will be more careful from this moment forward.

You can't hide a cell on its own. Hide does only work on Rows, Columns, or Sheets. Depending upon your set up, you could format it with white text so that it is invisible. Conditional Formatting could be used for this.

Other options off the top of my head are:
move it to and from a column/row that is always hidden,
put it on a separate sheet and report it on the current sheet (use an IF statement to decide whether it should be displayed or not),
put it in a text box that can be hidden or not.

Cheers, Rob.

Hi rob, Sorry but i managed to figure out what was causing the issue- I had mistyped "O" with a "0" for your code suggestion. My bad. Anyway, you have been a great help. Thanks

Nicely put Rob, Thanks. I'd like to know if i can use the same priniciple to hide columns. I have tried to replace the words "rows" to "columns" but i'm getting errors. I figure i might have gotten my syntax incorrect, care to enlighten me once again?

Oops... managed to solve the issue

Please Login or Register  to view this content.

I'm given another task today. I'm required to control the hiding of 200 rows depending on the input value given in a certain cell. This means that if the cell has a numerical value of 5, i should hide the other 195 rows. I'm guessing that using a "If" or a "for" loop might be needed. Can anyone guide me using a variable for the user input and some basic syntax to form the coding? I might be able to continue from there and post the updated codes here for all to see and learn.

Hi Maayub

You could use RESIZE to do this. Again, this would be easier with some more information...

If you want to keep row 5 and hide rows 6:200 you could use:

Please Login or Register  to view this content.

Hope this is enough of a clue. Clearly, you can put formulae in the place of the 6 and 195 (e.g. Range("A1").Value, etc)

Cheers, Rob.

Hi Rob, Thanks for the tip. I did notice the rows disappearing, but they do not resize when i change the values. As per your suggestion, instead of using a constant value in the cell, I referenced the function directly to the cell. In doing so, i hoped that the resizing change automatically. But this was not the case.

Please Login or Register  to view this content.

For the above code, When i set my a13 cell value as 5, rows 1- 5 are hidden. But when i change it to 3, nothing happen. I did include the part on the code that will effect any changes to the cell like you mentioned in your earlier posts, but nothing happen. Any idea what i'm doing wrong?

Thanks, Rob.

Ayub

Hi Ayub

Did you unhide all the rows first?

Try adding this line of code to unhide all the rows prior to re-hiding the ones you're interested in:

Please Login or Register  to view this content.

New complete code becomes:

Please Login or Register  to view this content.

If this doesn't solve it, try uploading the sheet and I'll have a look.

Best regards, Rob.

Hi rob,

Thanks for the reply. I've already been using many macros on the excel sheet that hides certain rows. I've attached a copy of the excel file for you viewing. please note that it is still unfinished and modifications will be done to it over the next few week. You may wish to pay attention to the portion in red and yellow. This is what i'm trying to achieve:

For the "Yellow" cell, whenever "weld surface" is selected, the region below it would be in pale pink. If any other choice is selected, the region below would be blue.

As for the "red" cell, if i enter 5 in it, i should only reveal 5 rows of the form and hide the rest, regardless of the choice in the "yellow" cell.


Thanks, Rob.

Ayub

Hi Rob,

You've done me a huge favour for revising the macros. Really appreciate it. Though i do have some queries on your coding. I would like to know how to interpret them.

Please Login or Register  to view this content.

It seems that if i were to use the resize function, i would need to hide the rows involved prior to executing the macro. Correct me if i'm wrong.

Please Login or Register  to view this content.

what does the line below your comment mean? I'm curious to know what do "<>" signify?

Do you how i may be able to force a cell to retain its text with reference to another cell. In the post where i had attached the NDE foem, I'm looking to force the "Method" field under "MPI" to always display "Continuous Wet Fluorescent" whenever the "PSL" field is either 3 or 4 but the user is free to choose either "Continuous Wet" or "Continuous Wet Fluorescent" for "PSL" field is NA, 1 or 2. For now, the user is still able to select 2 options if "PSL" is 3 or 4 and i had to put in a comment box to alert the user. I have tried to implement this using the data validation method but i'm unable use it together with the macro function.

Thank you so much, Rob.

Ayub

Hi Ayub

To answer your questions in order:
No - The Yellow cell macro (WELD) does the hiding for you.
When you change the Yellow cell, the macro hides or unhides the pink/blue cells. The HideRows macro hides all but the first row of these blocks, then sets the correct number of cells to be the same as the first row (hidden or visible). So:
1) Yellow cell changes and hides all pink cells and shows just 5 blue cells.
2) You change the red cell to 10.
3) HideRows hides all rows except the first pink and the first blue. (Note - the first pink cell was hidden earlier). So, now, only the first blue is visible. HideRows then expands its range and sets 10 blue rows to the same as the first blue row (visible) and sets 10 pink rows to the same as the first pink row (hidden).

<> means 'does not equal'. e.g. 1 + 1 <> 3. When using it in an IF statement, you can check whether something is not true. e.g. A = 3. IF A<>2 Then Msgbox "You don't have a pair."

For the MPI Method:
As part of your Worksheet_Change macro, have a check on the PSL. Add the following code to you Worksheet_Change macro:

Please Login or Register  to view this content.

This code:
1) Sets the value of the Method to CWF when 3 or 4 is entered; and
2) Changes the Validation in the Method cell to suit, so only the correct options are available.

You could achieve the same by having a range selected as the Validation, then having formulae in the list that either show or hide the values (although you'd still need the change macro to change the existing value if it was not valid).

Best regards, Rob.

hi rob,

I'm beginning to appreciate how useful excel is. Your explanation helps alot to understand the codes. I will read up on terms like CWF and post any questions regarding this in this thread. Thanks.

Ayub

Hi Ayub

CWF - Continuous Wet Fluorescent!

Regards, Rob.

Hi rob,

Thanks for reminding me on what CWF was, i must have missed it. I wish to query on some details on the modified validation of cells. In post #20, i understand that the target is only 1 single reference point. Can i also modify validation of cells if i assign multiple targets( say 2 reference points)? I'm trying to write a conditional statement which checks for PSL=2 and surface condition= weld surface together and then forces the method field under MPI to offer only CWF. I tried to modify the codes but i'm unable to specify 2 separate target addresses. Any ideas to implement this?

Also, I managed to speed up the macro processing( especially since i have many macros running concurrently, depending on one another) using these codes:

Please Login or Register  to view this content.

Thanks.


Ayub

Are there anyone else who may advise me on addressing this issue?

Hi Ayub

Sorry - I've been busy at work and not been on the forum for a good week or so.

In my code above (Post #20) you can add this requirement. The new code becomes:

Please Login or Register  to view this content.

This is the line that has changed:

Please Login or Register  to view this content.

Notice the addition of the (... And ...). This is how you check two cells at once. Also notice, we are not using the Target here, just normal Range statements.

Lastly - did you spot the underscore after the Or: _

This allows you to continue writing the code on the next line. It is treated as one line of code. This is useful when you have a long piece of code that scrolls past the end of the edit window, or you want to separate out pieces of code that are related, such as a bunch of Or/And statements.

I don't have an up to date copy of the spreadsheet, so I can't incorporate it for you. If you get stuck, upload the sheet and I'll have a look.

Best regards, Rob.

Hi Rob,

Sorry but i have been also busy with work so i had no time to reply to your post. You have provided me a good way to tie in 2 conditional statements and a way to reduce a long line of the codes. Will be helpful in writing macros in the future. Thanks to your help, i was able to make many refinements to the form since the last time. I will incorporate the new codes and upload it here within this week or so.

A big thanks,

Ayub

Hi rob,

As promised i've attached a copy of the fully modified and functional form that i've been working on. There is a small issue that i was hoping you can guide me, once again. In the form, under the turquoise field is a drop down menu. There are a few hundred values in the list from M1XXX rev. X to M4XXX rev.X. I'm trying to write another macro that will be able to identify only those values from M3XXX rev. X to M4XXX rev. X and use it to hide the whole MPI section of the form only. I now need to know how to just detect the first 2 characters from the list, afterwhich i can incorporate the hide rows codes.

QAQC-FORM.xlsm


Thanks a million,

Ayub

Any other forum users who may help me to write a macro to detect the first 2 characters in the dropdown menu to effect an action in the worksheet?

Thank, Rob. I will do as you have requested.