Hide / Unhide Rows

[vmjan02]

In have a ComboBox1 in a sheet and once the user selects the value from this ComboBox1 the value is displayed on cell E5.

Is there a better way, as I am trying to hide the rows and unhide the rows with the selection of ComboBox1. In ComboBox1 there more than 20 values

currently I have the below code.

Private Sub ComboBox1_Change()
Sheets("Summarised SiteWise").Range("E5").Value = ComboBox1.Value

If Sheets("Summarised SiteWise").Range("E5").Value = "All" Then
Worksheets("Summarised SiteWise").Rows("9:100").EntireRow.Hidden = False
End If

If Sheets("Summarised SiteWise").Range("E5").Value = "Alight Total" Then
Worksheets("Summarised SiteWise").Rows("9:9").EntireRow.Hidden = False
Worksheets("Summarised SiteWise").Rows("10:40").EntireRow.Hidden = True
End If

If Sheets("Summarised SiteWise").Range("E5").Value = "Aon Hewitt Total" Then
Worksheets("Summarised SiteWise").Rows("10:10").EntireRow.Hidden = False
Worksheets("Summarised SiteWise").Rows("9:9").EntireRow.Hidden = True
Worksheets("Summarised SiteWise").Rows("11:10").EntireRow.Hidden = True
End If


End Sub

 

[Joe4]

You can make things a little shorter by using a Case statement (see here: MS Excel: How to use the CASE Statement (VBA)).

So the code you wrote would look like this:

Private Sub ComboBox1_Change()
Sheets("Summarised SiteWise").Range("E5").Value = ComboBox1.Value

Select Case Sheets("Summarised SiteWise").Range("E5").Value
    Case "All"
        Worksheets("Summarised SiteWise").Rows("9:100").EntireRow.Hidden = False
    Case "Alight Total"
        Worksheets("Summarised SiteWise").Rows("9:9").EntireRow.Hidden = False
        Worksheets("Summarised SiteWise").Rows("10:40").EntireRow.Hidden = True
    Case "Aon Hewitt Total"
        Worksheets("Summarised SiteWise").Rows("10:10").EntireRow.Hidden = False
        Worksheets("Summarised SiteWise").Rows("9:9").EntireRow.Hidden = True
        Worksheets("Summarised SiteWise").Rows("11:100").EntireRow.Hidden = True
End Select

End Sub

and you can easily add more "Case" blocks.

Also, I find that you can shorten it a little more by unhidden everything from the start, and then just hide what you need to.
Then you do not need to have a "unhide" clause in each Case, just tell it which rows to hide, i.e.

Private Sub ComboBox1_Change()
Sheets("Summarised SiteWise").Range("E5").Value = ComboBox1.Value

'Unhide all possible rows at the very beginning
Worksheets("Summarised SiteWise").Rows("9:100").EntireRow.Hidden = False

Select Case Sheets("Summarised SiteWise").Range("E5").Value
    Case "Alight Total"
        Worksheets("Summarised SiteWise").Rows("10:40").EntireRow.Hidden = True
    Case "Aon Hewitt Total"
        Worksheets("Summarised SiteWise").Rows("9:9").EntireRow.Hidden = True
        Worksheets("Summarised SiteWise").Rows("11:100").EntireRow.Hidden = True
End Select

End Sub

 

[Fluff]

You can also simplify a bit more using a With statement like

Private Sub ComboBox1_Change()

With Sheets("Summarised SiteWise")
   .Range("E5").Value = ComboBox1.Value

   'Unhide all possible rows at the very beginning
   .Rows("9:100").EntireRow.Hidden = False
   
   Select Case .Range("E5").Value
       Case "Alight Total"
          .Rows("10:40").EntireRow.Hidden = True
       Case "Aon Hewitt Total"
          .Rows("9:9").EntireRow.Hidden = True
          .Rows("11:100").EntireRow.Hidden = True
   End Select
End With
End Sub

 

[vmjan02]

@Joe4 and @Fluff

thanks as this select case is all perfect. thanks



I tried this below code, it is working all good, but is hide's row 5, instead of multiple rows from Rng.
If this works, it will be great. (currently I am hiding the rows, later can unhide it)

Any suggestion

Sub HideRowsBy()

Dim l As Long
Dim Rng As Range
Dim WorkRng As Range

l = Sheets("Summarised SiteWise").Range("e9").End(xlDown).Row

Set Rng = Sheets("Summarised SiteWise").Range("e9" & l) 
Set WorkRng = Sheets("Summarised SiteWise").Range("e5")

For Each Rng In WorkRng
    If Rng.Value = WorkRng Then
        Rng.EntireRow.Hidden = True  'We can change it to false later
    End If
Next
End Sub

 

[Joe4]

You have a few issues there. I think I see what you are trying to do. Try this:

Sub HideRowsBy()

Dim l As Long
Dim Rng As Range
Dim Cell as Range
Dim WorkRng As Range

l = Sheets("Summarised SiteWise").Range("e9").End(xlDown).Row

Set Rng = Sheets("Summarised SiteWise").Range("e9:e" & l) 
Set WorkRng = Sheets("Summarised SiteWise").Range("e5")

For Each Cell In Rng
    If Cell.Value = WorkRng Then
        Cell.EntireRow.Hidden = True  'We can change it to false later
    End If
Next
End Sub

 

[vmjan02]

thanks @Joe4 it seems it is not hiding any row or rows.

 

[vmjan02]

@Joe4

Modified the code a bit and its working all prefect. thanks for the help.

Sub HideRowsBy()

Dim l As Long
Dim Rng As Range
Dim Cell As Range
Dim WorkRng As Range

Sheets("Summarised SiteWise").Rows("9:500").EntireRow.Hidden = False

l = Sheets("Summarised SiteWise").Range("D9").End(xlDown).Row

Set Rng = Sheets("Summarised SiteWise").Range("D9:d" & l)
Set WorkRng = Sheets("Summarised SiteWise").Range("e5")

For Each Cell In Rng
    If Cell.Value <> WorkRng Then
        Cell.EntireRow.Hidden = True  'We can change it to false later
    End If
Next
End Sub

 

[Joe4]

Glad you got it working.
I see the main change that you made was changing the column you are looping through from column E to column D.

 

[vmjan02]

yes, its true.