Stuck in a loop VBA

[ipon70]

I got this code from here to use on my sheet.

Sub Tuesday_Update()
Sheets("Tuesday").Unprotect Password:="123"
BeginRow = 1
EndRow = 745
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
Sheets("Tuesday").Protect Password:="123"
End Sub

Column C is set to use 1's and 0's as the trigger. Once a selection is made then that column for that section changes to 1's or 0's.
EXAMPLE: C6:C31 holds 25 potential spots for classes, but the class may only allow 12 people. So if the person picks that class, then C17:C31 changes to 0's and should hide with the code, leaving C1:C16 visible. The next section has a different selectable class, C38:C62 and that might have a class that only allows 4 people, so while there is 25 openings, after C42:C62 will change to 0's and should hide, leaving C33:C41 visible, along with the previous. In a nut shell hide any row with a 0 in column C.

The issue I am having is that is just loops and never ends...this should be a super-fast check and update, but is just hanging, and when I debug its the "End If" section it complains about.

Can someone help me trouble shoot this.

Thanks,

 

[dmt32]

Hi,
try a different approach to hiding the rows & see if this resolves your issue

Sub Tuesday_Update()
    Dim c As Range, HideRange As Range, DataRange As Range
    
    Const BeginRow As Long = 1, EndRow As Long = 745, ChkCol As Long = 3

    With Worksheets("Tuesday")
        .Unprotect Password:="123"
        Set DataRange = .Cells(BeginRow, ChkCol).Resize(EndRow)
    End With
    
    DataRange.EntireRow.Hidden = False

    For Each c In DataRange.Cells
        If c.Value = 0 Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
    
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True

End Sub

Dave

 

[ipon70]

See if this makes any difference:

Sub Tuesday_Update()

Dim BeginRow As Long
Dim EndRow As Long
Dim ChkCol As Long
Dim RowCnt As Long
Dim ctr As Long

Sheets("Tuesday").Unprotect Password:="123"

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

BeginRow = 1
EndRow = 745
ChkCol = 3
ctr = 1

For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    End If
    ctr = ctr + 1
    If ctr > 999 Then
        MsgBox "Max loops hit"
        Exit For
    End If
Next RowCnt

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Sheets("Tuesday").Protect Password:="123"

End Sub

If it does not, then I fear it may have more to do with the combination of the size of your workbook and the processing power of your computer.
You may want to make sure all other applications are closed when you try to run it, and see if that makes any difference.

YOU ARE A GOD!!! That was immediate, took about 1 second to run.....Thank you thank you thank you thank you!!!!!

 

[ipon70]

Thank

Hi,
try a different approach to hiding the rows & see if this resolves your issue

Sub Tuesday_Update()
    Dim c As Range, HideRange As Range, DataRange As Range
   
    Const BeginRow As Long = 1, EndRow As Long = 745, ChkCol As Long = 3

    With Worksheets("Tuesday")
        .Unprotect Password:="123"
        Set DataRange = .Cells(BeginRow, ChkCol).Resize(EndRow)
    End With
   
    DataRange.EntireRow.Hidden = False

    For Each c In DataRange.Cells
        If c.Value = 0 Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
   
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True

End Sub

Dave

Thank you for your response also, and yours would probably work also, but I did implement the other code and it worked. Had to move the "automatic" reenable to a different location but beyond that worked perfect.

 

[Joe4]

YOU ARE A GOD!!! That was immediate, took about 1 second to run.....Thank you thank you thank you thank you!!!!!

You are welcome.
Glad I was able to help.

Note that the two recent changes I made were:
1. Disabling event code from running while this code is running. I know you said you didn't have any, but it doesn't hurt to add it.
2. Disabled all calculations until the procedure is complete. This prevents any intermediate calculations that might be running while the code is to wait until the end.

These is often helpful in speeding things along (along with the supressing screen updating which I did in the earlier iteration of the code), especially when dealing with loops, which are notoriously slow.