Hide/Unhide with Multiple Variables

[Chelsea Bruner]

I want to hide and unhide rows based on two criteria.

Such as, in J2 it has a value of 2 that hides rows 4-55 and then J4 has a value of 1 that can hide 108-147. I can't seem to figure this out.

 

[Luke M]

Hi Chelsea, and welcome to the forum.
Code would be fairly straight-forward. Is there a particular part you were stuck with?

Sub ExampleHiding()

'Check if J2 = 2
Range("4:55").EntireRow.Hidden = (Range("J2").Value = 2)
'Check if J4 = 1
Range("108:147").EntireRow.Hidden = (Range("J4").Value = 1)

End Sub

 

[Chelsea Bruner]

I was having trouble combining the two functions that I wanted it to do. I'm fairly new to coding in Excel. Like I've been doing it two days. So its confusing at times.

Here's the coding I came up with that works for the first set but when I added the second, it stopped working.

Option Explicit
Sub Worksheet_Change(ByVal target As Range)
'Select lots
If Range("L2").Value = 1 And Range("J2").Value = 1 Then
  Rows("4:147").EntireRow.Hidden = False
  Rows("17:147").EntireRow.Hidden = True
End If

If Range("L2").Value = 2 And Range("J2").Value = 1 Then
  Rows("4:147").EntireRow.Hidden = False
  Rows("4:55").EntireRow.Hidden = True
  Rows("69:147").EntireRow.Hidden = True
End If

End Sub

 

[Luke M]

Ah, I see. Yes, the way you've got it written, you have to write code to both hide and unhide the rows. Give mine a shot. Since the Hidden object is looking for a Boolean setting, we can let the condition directly set the hide state, and you save on how much code you have to write.

 

[Chelsea Bruner]

your code isn't working.

 

[Luke M]

Explain further? What isn't working? Nothing happens, an error appears, something else happens...?

 

[NARAYANK991]

I want to hide and unhide rows based on two criteria.

Such as, in J2 it has a value of 2 that hides rows 4-55 and then J4 has a value of 1 that can hide 108-147. I can't seem to figure this out.

Hi ,

If we go by exactly what you have posted here , we need to do the following :

1. If J2 has the value 2 , then we hide rows 4 through 55.

What do you want done when J2 does not have the value 2 i.e. J2 has any other value ?

2. If J4 has the value 1 , then we hide rows 108 through 147.

There are 2 issues here :

• If due to J2 having 2 , rows 4 through 55 have been hidden , putting the value 1 in J4 is not straightforward , since J4 will be hidden. When do you want rows 4 through 55 to be unhidden , made visible ?
• Are these the only two groups of rows which need to be hidden ? Does this mean that when one group is hidden , at the same time the other group should be unhidden ?
• On the same lines as above , what do you want done when J4 does not have the value 1 i.e. J4 has any other value ?

Narayan