Hiding Unhiding Rows based on Text condition

[Winner_texas]

Dear all,

I am trying to find a way to hide and unhide rows based on Textual condition.

I am visualizing 3 checkboxes. When the checkbox is checked, it will hide based on aCondition. And similarly for the other two checkboxes.

The condition is whether the row contains the word "Current" - Condition for the first checkbox

The condition is whether the row contains the word "Improvement" - Condition for the second checkbox

The condition is whether the row contains the word "new" - Condition for the third checkbox

If there is any other simple way to do this, please let me know. I dont want to use the Group/Ungroup option because it doesnt look good and can be confusing. Thanks a lot. Please see the file attached.

[Leith Ross]

Hello Winner_texas,

Which row or rows are connected to each check box?

[Winner_texas]

Hello Winner_texas,

Which row or rows are connected to each check box?

They are currently not connected. Thats what I am asking help for.

Eventually,

When I click on Checkbox 1, it should hide all the rows which contain "Current" in Column A.

When I click on Checkbox 2, it should hide all the rows which contain "Improv" in Column A.

When I click on Checkbox 3, it should hide all the rows which contain "New" in Column A.

Also, when I unclick on the checkbox, it should show those rows which were previously hidden.

If there is any confusion, please ask. I am monitoring this thread all times. Thanks a lot.

[Winner_texas]

I found this code on this forum and I am thinking that what I am asking for could be a variation of this? Thanks again in advance.

Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub

[Leith Ross]

Hello Winner_Texas,

When hiding rows using worksheet controls, it is best to dedicate a row to the controls at the top of the worksheet. If you don't the controls will be hidden along with the row they belong to.

I have placed the controls on row 1 and frozen the row so it is always visible. The Check Boxes call two different macros. One to hide the rows and another to restore them. Here is the code that has been added to the attached workbook.

Module1 Code

Public HiddenRng(2) As Range

Sub HideRows(ByVal MatchTerm As String, ByVal CtrlId As Integer)

  Dim Cell As Range
  Dim HideRng As Range
  Dim I As Integer
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    I = CtrlId - 1
    
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
    
      For R = Rng.Row To (Rng.Rows.Count + Rng.Row - 1)
        Set Cell = Wks.Cells(R, 1)
        If HiddenRng(I) Is Nothing Then Set HiddenRng(I) = Cell
        If InStr(1, Cell.Text, MatchTerm, vbTextCompare) Then
           Set HiddenRng(I) = Union(HiddenRng(I), Cell)
        End If
      Next R
    
    HiddenRng(I).EntireRow.Hidden = True
    
End Sub

Sub ShowRows(ByVal CtrlId As Integer)
  If Not HiddenRng(CtrlId - 1) Is Nothing Then
     HiddenRng(CtrlId - 1).EntireRow.Hidden = False
  End If
End Sub

Sheet1 Code for the Check Boxes

Private Sub CheckBox1_Change()
  If CheckBox1 = True Then
     HideRows CheckBox1.Caption, 1
  Else
     ShowRows 1
  End If
End Sub

Private Sub CheckBox2_Change()
  If CheckBox2 = True Then
     HideRows CheckBox2.Caption, 2
  Else
     ShowRows 2
  End If
End Sub

Private Sub CheckBox3_Change()
  If CheckBox3 = True Then
     HideRows CheckBox3.Caption, 3
  Else
     ShowRows 3
  End If
End Sub

[Winner_texas]

Hello Winner_Texas,

When hiding ........
End Sub
[/code]

Thanks a lot for writing this code and for your time. Few points -

1. How do I change the name of the the checkboxes in Cell A1 in your solution? For example if I would like to change it to "Hide Current" instead of "Current"..and similarly for others.
2. Can you give me a little logic around your code lines so that if I need to edit/learn, I can do it myself. For example, what if I instead of "Improv", I have "Improvement" in all the lines. I didnt see a reference to word itself in the code. I am wondering if it is dependent on the control name itself.
3. Later on in this development, if I have another condition such as - Hide all rows which have "Final" word in the text. How do I replicated what you have done.

Once again, this is great. My questions may be very naive since I have never done coding but willing to learn if I can make tools such as these.

Many Thanks

[Winner_texas]

Just thinking about it more, I believe that I am seeing that problem because the Search Term is not the Control's Caption anymore?

[Leith Ross]

Hello Winner_texas,

Strange, I downloaded the file and it is working correctly.

[Leith Ross]

Hello Winner_texas,

Here is the macro. It simply activates the other check boxes . Hiding all the other data leaves only the Cash Flows (if any are present).

Private Sub CheckBox5_Click()
  If CheckBox5 = True Then
     CheckBox1.Value = True
     CheckBox2.Value = True
     CheckBox3.Value = True
     CheckBox4.Value = True
  Else
     CheckBox1.Value = False
     CheckBox2.Value = False
     CheckBox3.Value = False
     CheckBox4.Value = False
  End If
End Sub

[Winner_texas]

Hello Winner_texas,

Here is the macro. It simply activates the other check boxes . Hiding all the other data leaves only the Cash Flows (if any are present).

Private Sub CheckBox5_Click()
  If CheckBox5 = True Then
     CheckBox1.Value = True
     CheckBox2.Value = True
     CheckBox3.Value = True
     CheckBox4.Value = True
  Else
     CheckBox1.Value = False
     CheckBox2.Value = False
     CheckBox3.Value = False
     CheckBox4.Value = False
  End If
End Sub

Leith, Thanks for the above solution and I apologize for continuous questions. The above solution will not work in absence of the above 4 checkboxes. It would be good to have a macro which only looks for the particular text on the basis of which we want to show the rows and hides everything else. So, to paraphrase, if the 4 initial control boxes were not there, how will we still accomplish the same task. Thanks!

[Leith Ross]

Hello Winner_Texas,

I wanted to make this change as simple as possible. So, I added another argument on to the HideRows macros. The arguments now are: Match Term (string), ControlID (integer), HideOnMatch (boolean). The last argument determines if the row is to be hidden when the match term, what you are searching for, is found (True) or to hide all other rows that do not match the search term (False). Here are the updates that have been added to the new workbook.

Module1 Macro Code

Public HiddenRng(4) As Range

Sub HideRows(ByVal MatchTerm As String, ByVal CtrlId As Integer, HideOnMatch As Boolean)

  Dim Cell As Range
  Dim HideRng As Range
  Dim I As Integer
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    I = CtrlId - 1
    
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
    
      For R = Rng.Row To (Rng.Rows.Count + Rng.Row - 1)
        Set Cell = Wks.Cells(R, 1)
        If HiddenRng(I) Is Nothing Then Set HiddenRng(I) = Cell
        If InStr(1, Cell.Text, MatchTerm, vbTextCompare) Then
          If HideOnMatch Then Set HiddenRng(I) = Union(HiddenRng(I), Cell)
        Else
          If Not HideOnMatch Then Set HiddenRng(I) = Union(HiddenRng(I), Cell)
        End If
      Next R
    
    HiddenRng(I).EntireRow.Hidden = True
    
End Sub

Sub ShowRows(ByVal CtrlId As Integer)
  If Not HiddenRng(CtrlId - 1) Is Nothing Then
     HiddenRng(CtrlId - 1).EntireRow.Hidden = False
  End If
End Sub

Sheet1 CheckBox Macro Code

Private Sub CheckBox1_Change()
  If CheckBox1 = True Then
     HideRows "Current", 1, True
  Else
     ShowRows 1
  End If
End Sub

Private Sub CheckBox2_Change()
  If CheckBox2 = True Then
     HideRows "Improv", 2, True
  Else
     ShowRows 2
  End If
End Sub

Private Sub CheckBox3_Change()
  If CheckBox3 = True Then
     HideRows "New", 3, True
  Else
     ShowRows 3
  End If
End Sub

Private Sub CheckBox4_Change()
  If CheckBox4 = True Then
     HideRows "Final", 4, True
  Else
     ShowRows 4
  End If
End Sub

Private Sub CheckBox5_Click()
  If CheckBox5 = True Then
     HideRows "Cash Flow", 5, False
  Else
     ShowRows 5
  End If
End Sub

.

[Winner_texas]

So I took the "asterik" out from the code and the macro is working now.

"Private Sub CheckBox1_Change()
If CheckBox1 = True Then
HideRows "improv*", 1
Else
ShowRows 1
End If
End Sub"

[Winner_texas]

So I took the "asterik" out from the code and the macro is working now.

"Private Sub CheckBox1_Change()
If CheckBox1 = True Then
HideRows "improv*", 1
Else
ShowRows 1
End If
End Sub"

So this Macro works beautifully and very easy to edit. I was able to add another control box to hide specific rows and its working without any problems.

I would like to add to it now. Hope it will be very similar to the code above. If I want to "Show Only" based on some text, how would I do it. I added some rows which say "Cash Flow" in it. When I click on the 5th Control Box, it should only show the rows which has Cash Flow in the first column. I have added the box to save your time. Once again, thanks for your help.

[Winner_texas]

I am currently playing with it. Thanks a lot for your time and help. Also, thanks for simplifying it for me. Best regards.