Thread: 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:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$9" Then
 Call HIDEROWUSINGTEXT
 End If
 
End Sub

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

Sub Hide_Details()

If Range("Z14").Value = "Yes" Then
Columns("AA:AP").EntireColumn.Hidden = True
Else
Columns("AA:AP").EntireColumn.Hidden = False
End If

End Sub

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:

Sheets("Sheet1").Rows(6).Resize(195).Hidden = True

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.

Sub ROWRESIZE()
Sheets("Sheet1").Rows(1).Resize(Range("A13").Value).Hidden = True
End Sub

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