How to Hide and unhide rows by input provided in terms of Y/N ?

[trustmeasfrnd]

Hi,
how to Hide or unhide specific rows by inputting Y = Yes to Hide N = No to unhide.
like in excel sheet if in B5 input is "Y" it hide row 18 to row 25 and when i input "N" it unhide those rows.
does it need to use VBA or it can be done without VBA?

thanks and regards,
Abhisekh

 

[wideboydixon]

It needs VBA. Right click the sheet name and select "View Code" then paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)


If Application.Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
Range("18:25").EntireRow.Hidden = Range("B5").Value = "Y"


End Sub

WBD

 

[trustmeasfrnd]

WBD,
wow Thanks that worked.Can you explain what this code means so i ve better understanding.
thanks and regards,
Abhisekh

 

[wideboydixon]

If Application.Intersect(Target, Range("B5")) Is Nothing Then Exit Sub

The line above checks whether the range that has changed includes your cell (B5). If not, it quits out.

Range("18:25").EntireRow.Hidden = Range("B5").Value = "Y"

This line sets the Hidden property of the rows. This can be True or False. This part:

Range("B5").Value = "Y"

Returns True if you put a Y in B5 (so it hides the rows) or False for anything else (so it unhides the rows).

WBD

 

[trustmeasfrnd]

WBD,
Thanks again.

regards,
Abhisekh