Change Event - Hide Range

[Hainesy]

Hi all,

I have the following VBA code that I am having issues with, ive pieced it together from various sources but it doesn't quite work as it should...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
 
If Not Intersect(Range("H6"), Target) Is Nothing Then
  Select Case UCase(Range("H6").Value)
  
    Case ""
        Rows("9:32").Hidden = True
                
    Case "A"
        Rows("9:19").Hidden = True
      
    Case "B"
        Rows("20:32").Hidden = False
    
  End Select

End If

Application.ScreenUpdating = True
 
End Sub

Basically H6 will only be either A, B or Blank.

Depending on the selection in H6, certain rows need to be visible.

H6 by default will be blank, and rows 9:32 need to be hidden from the off.

Any help would be appreciated and I am sure this will be a quick fix for someone!

Thanks in advance!

 

[Joe4]

So what exactly is the question/issue?

Note, since you may be hiding the entire range in certain situations, you will want two statements in each of your "A" and "B" scenarios, one that tells it what range is hidden, and one that tells it which range is visible in each scenario. Otherwise, as you have written the code now, if you start off with rows 9:32 hidden, the commands for scenario "A" (as currently written), do not unhide any rows.

 

[Fluff]

Bit of a guess, but try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
 
If Not Intersect(Range("H6"), Target) Is Nothing Then
   Rows("9:32").Hidden = True
   Rows("9:19").Hidden = LCase(Target) = "b"
   Rows("20:32").Hidden = LCase(Target) = "a"
End If

Application.ScreenUpdating = True
 
End Sub

 

[Hainesy]

Hi both,

Apologies, I was a little vague!

I've tried the above code provided by Fluff, but I seem to be getting the same issues that I was getting with my original code.

The logic for a blank value in H6 (hide rows 9:32) works fine. However the logic for values A & B (make certain ranges visible depending on the value) do not work.

 

[Fluff]

What rows should be hidden if A is selected & what rows should be hidden if B is selected?

 

[Hainesy]

If BLANK Only Rows 9:32 should be hidden

If "A" Only Rows 20:32 should be hidden

If "B" Only Rows 9:19 should be hidden

Thanks

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
   If Not Intersect(Range("H6"), Target) Is Nothing Then
      Rows("9:32").Hidden = False
      Select Case LCase(Target)
         Case Empty
            Rows("9:32").Hidden = True
         Case "a"
            Rows("20:32").Hidden = LCase(Target) = "a"
         Case "b"
            Rows("9:19").Hidden = LCase(Target) = "b"
      End Select
   End If
Application.ScreenUpdating = True
End Sub

 

[Hainesy]

Still having the same issues with this code.

I've currently got the function working from a button, and it works fine, it just doesn't seem to like being a change event...

 

[Joe4]

I've currently got the function working from a button, and it works fine, it just doesn't seem to like being a change event...

Are you sure that you placed it in the correct Sheet module (it HAS to be in a sheet module, it won't work in a General module)?
Also, how exactly are you updating cell H6 to trigger the code? Remember, this type of code only runs when H6 is manually updated.
And is H6 part of a merged cell?

 

[Hainesy]

Hi Joe,

It is definitely in the sheet module, not a general module.
H6 currently contains a drop down validation list, where the user will select from the available options (A or B).
H6 is currently merged with I6, could this be an issue?

Thanks,