For Next Loop stops after first true result and ends the Loop

[programsam]

Greetings, I think the subject says it all but I'm having a difficult time getting this For Next loop to run through the entire range to hide all of the rows that meet a specific value.

Please advise Code below

Private Sub Worksheet_calculate()
Dim c As Range
Dim divSelection As String

'Set c = Me.Range("CG16")
divSelection = ActiveSheet.Range("CG16").Value

Application.ScreenUpdating = False

For Each c In Range("A19:A75") 'Cells(Rows.Count, "A").End(xlUp))
    If c.Value <> divSelection Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c

Application.ScreenUpdating = True

End Sub

 

[Snakehips]

@programsam That code looks fine and tests perfectly well for me.
Do you perhaps have a non-obvious difference in data that is causing your IF condition to return False?

 

[DanteAmor]

Hi @program. Thanks for posting on the forum.

The code does check the entire range of cells "A19:A75", but it is only executed when the Calculate event is fired, and this occurs when you change a formula on the sheet or change any cell that affects the result of a formula on the sheet.

In order to better understand your need, I would like to ask a few questions:

1. Could you explain what you have in cell CG16, is it a value or is it a formula, if it is a formula you can put that formula here.

2. What do you have in cells A19 to A75, do you have formulas or values, can you give some examples. If it is a formula you can put that formula here.

3. What is your need? Do you expect the code to execute when it happens? That is, what are you going to do in the sheet to make the code run?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[programsam]

Hi @program. Thanks for posting on the forum.

The code does check the entire range of cells "A19:A75", but it is only executed when the Calculate event is fired, and this occurs when you change a formula on the sheet or change any cell that affects the result of a formula on the sheet.

In order to better understand your need, I would like to ask a few questions:

1. Could you explain what you have in cell CG16, is it a value or is it a formula, if it is a formula you can put that formula here.

2. What do you have in cells A19 to A75, do you have formulas or values, can you give some examples. If it is a formula you can put that formula here.

3. What is your need? Do you expect the code to execute when it happens? That is, what are you going to do in the sheet to make the code run?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Man I feel like I'm about to learn something here.

CG16 is a value off of a Pivot Table. I reference it based on changes to selections on a slicer so the result changes based on slicer selections.

The range has an XLOOKUP formula that CG16 is trying to bump against in order to hide the rows.

=XLOOKUP(B19,'Hierarchy'!$B:$B,'Hierarchy'!A:A,"",0,1)

What I'm wanting is when the slicer is selected and changes the value in the pivot (CG16), for all rows in range A19-A75 with the value in CG16 to be hidden and when another slicer button is selected, for all rows to unhide and hide the associated rows again.

Hope this helps.

 

[DanteAmor]

It's hard for me to understand how your pivot table is in your sheet, how is your slicer, it would be better if you explain it with an image or with several images to understand what you select in your slicer and what data is moved in your sheet, or if you can share your file on the cloud.

I'll try to guess how your pivot table is. Assuming that when selecting a slicer, cell CG16 is updated, then change your code to the following event:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  
  If Not Intersect(Target, Range("CG16")) Is Nothing Then
    With Range("A19:A75")
      .EntireRow.Hidden = False
      For Each c In .Cells
        If c.Value <> Range("CG16").Value Then c.EntireRow.Hidden = True
      Next
    End With
  End If
End Sub

If the above doesn't work for you, I still assume, if you select a slicer and a column of the pivot table is updated, then use the following event, change "CQ:CQ" to the letters of the first column where you have your pivot table.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  
  If Not Intersect(Target, Range("CQ:CQ")) Is Nothing Then
    With Range("A19:A75")
      .EntireRow.Hidden = False
      For Each c In .Cells
        If c.Value <> Range("CG16").Value Then c.EntireRow.Hidden = True
      Next
    End With
  End If
End Sub

I hope one of the above 2 solutions works for you, if not, please share your file.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------