VBA Leveling my Selections- Unhides Rows

[Suggettm]

Hello,

I'm trying to create a selection tool when based on the criteria selected more rows become un-hidden. So far I have the below for my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'For On Property'


If Range("C7").Value = "YES" And Range("F7").Value = "NO" Then
Rows("9:10").EntireRow.Hidden = False
'For Above_Property- Drops down Company_Level'


ElseIf Range("F7").Value = "YES" And Range("C7").Value = "NO" Then
Rows("9:10").EntireRow.Hidden = True
Rows("12:13").EntireRow.Hidden = False

ElseIf Range("F7").Value = "YES" And Range("C7").Value = "NO" Then
Rows("9:10").EntireRow.Hidden = True
Rows("12:13").EntireRow.Hidden = False
ElseIf Range("F7").Value = "YES" And Range("C7").Value = "NO" And Range("F13").Value = "Division" Then
Rows("9:10").EntireRow.Hidden = True
Rows("12:13").EntireRow.Hidden = False
Rows("14:16").EntireRow.Hidden = False
Else
Rows("9:29").EntireRow.Hidden = True


End If


End Sub


Everything works up until the user has to select Division. Once Division is chosen in the Box Rows 14:16 do not unhide. The process would keep going because cell F13 can also contain Region, in the case of Rows 18:19 would then unhide.

Please help me out. I'm not sure why it is not unhiding the second set of cells.

 

[frank_AL]

So I see 2 issues with your code. In your first condition you test for C7="YES" and F7="NO". Since your last ElseIf also tests for those 2 cells having the same value the code finds your initial If statement to be true, executes the code below to Unhide Rows 9 & 10 and then exits the overall If statement never getting to your last ElseIf. So to correct that I have swapped places with those 2 conditions.

The second issue (that is not causing a problem) is that you have 2 ElseIFs that test for F7="YES" and C7="NO". I removed one of those ElseIFs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'For On Property'




If Range("F7").Value = "YES" And Range("C7").Value = "NO" And Range("F13").Value = "Division" Then
    Rows("9:10").EntireRow.Hidden = True
    Rows("12:13").EntireRow.Hidden = False
    Rows("14:16").EntireRow.Hidden = False
ElseIf Range("F7").Value = "YES" And Range("C7").Value = "NO" Then
    Rows("9:10").EntireRow.Hidden = True
    Rows("12:13").EntireRow.Hidden = False
ElseIf Range("C7").Value = "YES" And Range("F7").Value = "NO" Then
    Rows("9:10").EntireRow.Hidden = False
'For Above_Property- Drops down Company_Level'
Else
    Rows("9:29").EntireRow.Hidden = True
End If


End Sub

 

[Suggettm]

So I see 2 issues with your code. In your first condition you test for C7="YES" and F7="NO". Since your last ElseIf also tests for those 2 cells having the same value the code finds your initial If statement to be true, executes the code below to Unhide Rows 9 & 10 and then exits the overall If statement never getting to your last ElseIf. So to correct that I have swapped places with those 2 conditions.

The second issue (that is not causing a problem) is that you have 2 ElseIFs that test for F7="YES" and C7="NO". I removed one of those ElseIFs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'For On Property'




If Range("F7").Value = "YES" And Range("C7").Value = "NO" And Range("F13").Value = "Division" Then
    Rows("9:10").EntireRow.Hidden = True
    Rows("12:13").EntireRow.Hidden = False
    Rows("14:16").EntireRow.Hidden = False
ElseIf Range("F7").Value = "YES" And Range("C7").Value = "NO" Then
    Rows("9:10").EntireRow.Hidden = True
    Rows("12:13").EntireRow.Hidden = False
ElseIf Range("C7").Value = "YES" And Range("F7").Value = "NO" Then
    Rows("9:10").EntireRow.Hidden = False
'For Above_Property- Drops down Company_Level'
Else
    Rows("9:29").EntireRow.Hidden = True
End If


End Sub

Thanks Man! It works perfectly. I was probably going crazy so I had the Dup code in there, but it works like a charm thanks!

 

[frank_AL]

I know what you are saying about going crazy trying to find an answer to a problem and nothing seems to be working!!

One thing to consider, are you or the users choosing these answers via a pre-defined drop down or are they entering manually? My concern is that the code is checking for "Division". If DIVISION is entered it wouldn't match 'Division'.

If so you could make this change that would convert whatever is in cell F13 to Uppercase for evaluation purposes (not changing what is actually in the cell).

If Range("F7").Value = "YES" And Range("C7").Value = "NO" And UCase(Range("F13").Value) = "DIVISION" Then

 

[Suggettm]

I know what you are saying about going crazy trying to find an answer to a problem and nothing seems to be working!!

One thing to consider, are you or the users choosing these answers via a pre-defined drop down or are they entering manually? My concern is that the code is checking for "Division". If DIVISION is entered it wouldn't match 'Division'.

If so you could make this change that would convert whatever is in cell F13 to Uppercase for evaluation purposes (not changing what is actually in the cell).

If Range("F7").Value = "YES" And Range("C7").Value = "NO" And UCase(Range("F13").Value) = "DIVISION" Then

That's a good thought. I'm using a designated pick list and so far it works smoothly. Ohh baby it hides the cells based on the selected criteria. It works like a charm. And I got another macro that auto refreshes the data, and then hides rows with no data based on either a 0 or 1. I'm almost done updating my workbook. If I run into that problem I'll correct my code to what you said above.