Auto Hide/Unhide Rows dependant on Value

[spencee]

Hi,

So i'm struggling to get the correct code to allow me to hide/unhide the rows in my spreadsheet dependant on value.

In column F i have the option of Y/N and would like my spreadsheet to automatically hide something that has Y selected dependant on the value in column G and unhide anything with N. In column G I also have the option of Y/N. - I've tried to amend other code that i have stumbled across and write my own, but none of them seem to work.

For example (Col F / Col G)

(Y/N) - Hidden
(Y/Y) Unhidden
(N/Y) Unhidden
(N/N) Unhidden

If one of the clever people could point me in the right direction it would be much appreciated!

Thanks.

 

[Trevor_S]

Assuming that your data starts in row 2 (to allow for headers) and that there's no gaps in columns F and G - try this:

Sub test()
myRow = 2
Do[INDENT]myValues = Range("F" & myRow).Value & Range("G" & myRow).Value
If myValues = "" then exit do
If myValues = "YN" then[/INDENT]
[INDENT=2]Range("F" & myRow).EntireRow.Hidden = True[/INDENT]
[INDENT]Else[/INDENT]
[INDENT=2]Range("F" & myRow).EntireRow.Hidden = False[/INDENT]
[INDENT]End if
myRow = myRow = myRow + 1[/INDENT]
Loop
End Sub

 

[Peter_SSs]

I'm assuming
- Header row 1
- Data starts in column A
- No completely blank rows or columns within the data area.
- Column Z available to use as a helper.

Sub HideShow()
  Range("Z2").Formula = "=F2&""|""&G2<>""Y|N"""
  Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Z1:Z2"), Unique:=False
  Range("Z2").ClearContents
End Sub

 

[Trevor_S]

The two solutions posted so far use different methods of making the rows invisible. Mine hides them, Peter's filters them out of view. Do you have subtotal formulae in your spreadsheet - if so, the result returned by the formulae can differ. Rows filtered out of view are always excluded from subtotals, whereas rows hidden from view are only excluded if the function number (first argument in the Subtotal formula) is greater than 100.

Nothing wrong with using either method - its just something that you (might) need to be aware of...