Show/Hide WBS items depending on ComboBox in Excel VBA

Question

I want to be able to filter the WBS data of a worksheet with a combobox.

Choosing a work stage should result in only the products which have that work stage showing a status of In progress being visible. This work stage, and any earlier in progress ones, should also be visible.

In addition, if a product with the chosen work stage has a status of Finished, but contains any earlier work stages in progress, it should also be visible. Those earlier work stages should also be visible. The chosen work stage for this product must not be visible.

If a product doesn't have the chosen work stage at all, neither it, nor any of its work stages, should be visible.

Order rows should only be visible if they have visible products.

Before selection:

After selection:

Here is what I have tried to do:

Dim ws Worksheet
Set ws = ThisWorkbook.Sheets("DataSheet")

Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, 4).End(xlUp).row

ws.Rows("2:" & lastRow).Select
Selection.EntireRow.Hidden = True

Dim SrchRng As Range, cel As Range
Set SrchRng = ws .Range("A5", "I" & lastRow)

For Each cel In SrchRng
    If cel.Range("C1").Text = ComboBox1.Text And cel.Range("E1").Value <> "In progress" Then
        cel.EntireRow.Hidden = False
    End If
Next

This code only makes visible the chosen work stages which are in progress. I want the product row, order row, and all earlier in progress work stage rows, to be visible.

Answer 1

EDIT: (v0.3.2) Added a "show all" work stages option.

Seemed to be easy to do, but turned out to be a little bit tricky.

You need to save two row indexes as you loop through the rows: the current order row and the current product row. Only then can you correctly show the order if any of the products are shown.

Note that the constant declarations and long variable names make the code self-documenting.

v0.3.2:

• Show All - If you add another item to the combobox which doesn't start with "Work stage ", selecting it shows all the rows. This item can also be blank.

Code:

Option Explicit
'v0.3.2
Private Sub ComboBox1_Change()

  Const l_Order_      As String = "Order "
  Const l_Work_stage_ As String = "Work stage "
  Const l_In_progress As String = "In progress"
  Const i_Orders      As Long = 1
  Const i_Products    As Long = 2
  Const i_WorkStages  As Long = 3
  Const i_Progress    As Long = 5

  Dim rngCurrentRow As Range
  Dim lngFirstDataRow As Long
  Dim lnglastDataRow As Long
  Dim lngCurrentOrderRow As Long
  Dim lngCurrentProductRow As Long
  Dim boolShowProduct As Boolean

  Application.ScreenUpdating = False
  ' No need for sheet name since Me = active sheet
  lngFirstDataRow = WorksheetFunction.Match(l_Order_ & "*", Me.Columns(i_Orders), 0)
  lnglastDataRow = WorksheetFunction.Match("*", Me.Columns(i_Progress), -1)
  With Range(Me.Rows(lngFirstDataRow), Me.Rows(lnglastDataRow))
    If Not ComboBox1.Text Like l_Work_stage_ & "*" Then
      .EntireRow.Hidden = False
      GoTo ExitSub:
    Else
      .EntireRow.Hidden = True
      Set rngCurrentRow = .Rows(1)
    End If
  End With
  ' Loop through all data rows
  Do
    If rngCurrentRow.Columns(i_Orders) <> vbNullString Then
      lngCurrentOrderRow = rngCurrentRow.Row
      Set rngCurrentRow = rngCurrentRow.Offset(1)
    End If
    If rngCurrentRow.Columns(i_Products) <> vbNullString Then
      lngCurrentProductRow = rngCurrentRow.Row
      Set rngCurrentRow = rngCurrentRow.Offset(1)
    End If
    boolShowProduct = False
    ' Loop through consecutive non-empty progress/work stage rows
    Do Until rngCurrentRow.Columns(i_Progress) = vbNullString
      If rngCurrentRow.Columns(i_WorkStages) <= ComboBox1.Text _
      And rngCurrentRow.Columns(i_Progress) = l_In_progress _
      Then
        boolShowProduct = True
        Rows(rngCurrentRow.Row).Hidden = False
      Else
        ' Ignore
      End If
      Set rngCurrentRow = rngCurrentRow.Offset(1)
    Loop
    If rngCurrentRow.Columns(i_WorkStages).Offset(-1) < ComboBox1.Text Then
      ' Re-hide previous shown work stages for this product
      Range(Rows(lngCurrentProductRow), Rows(rngCurrentRow.Row)).Hidden = True
    ElseIf boolShowProduct Then
      Rows(lngCurrentOrderRow).Hidden = False
      Rows(lngCurrentProductRow).Hidden = False
    End If
  Loop Until rngCurrentRow.Row > lnglastDataRow
ExitSub:
  Application.ScreenUpdating = True

End Sub

Note: If you are curious about my variable naming convention, it is based on RVBA.

Answer 2

Here is my attempt at it. I have made some assumptions:

1. Column A holds Order numbers in Order x format where x is a number
2. Column B holds product name in Product x format where x is either x or y
3. Column C holds work stage name in Work stage x format where x is a number
4. Column D holds work stage status as either Finished or as In progress
5. ComboBox holds the work stage name in Work stage x format where x is a number

6. There are no empty rows

   Sub ShowHideStages()
       Dim oW As Worksheet: Set oW = ThisWorkbook.Worksheets("Sheet9")
       Dim iLR As Long: iLR = oW.Cells(Rows.Count, 4).End(xlUp).Row
       Dim oCB As OLEObject: Set oCB = oW.OLEObjects("ComboBox1")
       Dim iC As Long
       Dim iA As Integer
       Dim iFirstRow As Integer
       Dim iOStartRow As Integer
       Dim iPStartRow As Integer
       Dim bStageFound As Boolean
       Dim bProdFound As Boolean
       Dim bHideRow As Boolean
       Dim sRowsToHide As String
       Dim aTemp As Variant
   
       ' Unhide all rows
       oW.Rows("2:" & iLR).EntireRow.Hidden = False
   
       ' Find the first instance of Order
       iFirstRow = Columns("A").Find(what:="*", After:=Columns("A").Cells(1, 1), LookIn:=xlValues).Row
   
       ' If dropdown value is empty then skip the process of hiding rows
       If Len(Trim(oCB.Object.Text)) > 0 Then
           With oW
   
               ' Loop through all rows
               For iC = iFirstRow To iLR
   
                   ' Lets loop through current order
                   bStageFound = False
                   iOStartRow = iC
                   Do
   
                       iC = iC + 1
                       sRowsToHide = ""
                       iPStartRow = iC
                       bProdFound = False
   
                       ' Now lets loop through each product
                       Do
                           iC = iC + 1
                           bHideRow = False
   
                           ' Check if we need to hide the current row
                           If CInt(Right(Trim(Range("C" & iC).Text), 1)) <= CInt(Right(Trim(oCB.Object.Text), 1)) And LCase(Trim(Range("D" & iC).Text)) = "finished" Then
                               bHideRow = True
                           ElseIf CInt(Right(Trim(Range("C" & iC).Text), 1)) > CInt(Right(Trim(oCB.Object.Text), 1)) Then
                               bHideRow = True
                           End If
   
                           ' Check if work stage is the expected stage
                           If Range("C" & iC).Text = oCB.Object.Text And LCase(Trim(Range("D" & iC).Text)) <> "finished" Then
                               bProdFound = True
                               bStageFound = True
                           End If
   
                           ' Set rows to hide string
                           If bHideRow Then
                               If Len(Trim(sRowsToHide)) = 0 Then
                                   sRowsToHide = CStr(iC)
                               Else
                                   sRowsToHide = sRowsToHide & "," & CStr(iC)
                               End If
                           End If
   
                       Loop While Len(Trim(Range("C" & iC).Offset(1, 0).Text)) <> 0
   
                       ' Check if product was found
                       If Not bProdFound Then
                           ' It wasn't so set the string to hide product
                           sRowsToHide = CStr(iPStartRow) & ":" & CStr(iC)
                       End If
   
                       ' Hide specified rows .. if specified
                       If Len(Trim(sRowsToHide)) > 0 Then
                           If InStr(1, sRowsToHide, ":") > 0 Then
                               Range(sRowsToHide).EntireRow.Hidden = True
                           Else
                               aTemp = Split(sRowsToHide, ",")
                               For iA = 0 To UBound(aTemp)
                                   Rows(Trim(aTemp(iA))).EntireRow.Hidden = True
                               Next
                           End If
                           sRowsToHide = ""
                       End If
   
                   Loop While Len(Trim(Range("A" & iC).Offset(1, 0).Text)) = 0 And iC < iLR
   
                   ' Check if order was found
                   If Not bStageFound Then
                       ' It wasn't so lets set the string to hide the order
                       sRowsToHide = CStr(iOStartRow) & ":" & CStr(iC)
                   End If
   
                   ' Hide the order if we need to
                   If Len(Trim(sRowsToHide)) > 0 Then
                       Range(sRowsToHide).EntireRow.Hidden = True
                   End If
   
           End With
   
       End If
   
   End Sub
   

NOTE: UDF also caters for an empty selection in ComboBox. In this case all rows are displayed