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,

 

[Joe4]

What is calling this code to run?
Do you have any event procedure code on this sheet?
If so, please post that code.

 

[ipon70]

What is calling this code to run?
Do you have any event procedure code on this sheet?
If so, please post that code.

Actually just a button attached to the macro. So basically a shape and assigned macro.

 

[Joe4]

It seems to work just fine for me.

Do you have any errors in the cells in column C?

 

[ipon70]

It seems to work just fine for me.

Do you have any errors in the cells in column C?

None, it worked really quick until it had hidden rows before it ran, then it started complaining. So when I pick 24 classes for the day (max) and C1:C745 are visible then it runs just fine. If I pick 6 classes for day, then it starts doing the loop thing.

 

[Joe4]

Is it "hanging", or are you getting an error?
If you are getting an error, what is the exact error message?

Here is a version of the code that will quit after 999 loops, so you should not hang up. See what happens if you run this:

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.ScreenUpdating = False

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.ScreenUpdating = True

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

End Sub

 

[ipon70]

Is it "hanging", or are you getting an error?
If you are getting an error, what is the exact error message?

Here is a version of the code that will quit after 999 loops, so you should not hang up. See what happens if you run this:

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.ScreenUpdating = False

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.ScreenUpdating = True

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

End Sub

Ok, tried your code and its about the same result, takes forever to run (blue ball rolling around in circles, and sheet is froze) but at least has an end, and pops up the message. Once you click "ok", then the sheet updates.
I am really confused as to what is going on. Any other ideas??

Here is what column C looks like for 4 classes. The first 1's and 0's represent what the code is looking at for classes 1-3, The zero's in red represent class 4 which would normally be hidden and the start of classes 4-24 having all zero's.

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

 

[Joe4]

It really sounds like you are getting some sort of interference going on somewhere, that is affecting workbook performance.
- How big is your workbook?
- Where exactly is this workbook you are running this against, as related to the computer you are running it from? Is your workbook stored locally on your computer?
- Are you 100% sure that there is no other VBA code anywhere in this workbook (take a look at EVERY module, including the Sheet modules, in the VB Editor)?

 

[ipon70]

It really sounds like you are getting some sort of interference going on somewhere, that is affecting workbook performance.
- How big is your workbook?
- Where exactly is this workbook you are running this against, as related to the computer you are running it from? Is your workbook stored locally on your computer?
- Are you 100% sure that there is no other VBA code anywhere in this workbook (take a look at EVERY module, including the Sheet modules, in the VB Editor)?

the overall workbook is pretty big, about 89megs. The sheet runs locally off my desktop. I have a lot of other VBA code in this workbook, but none that automatically run (i.e. change, selectionchange). All code has to be triggered manually from a button. The only other code that would affect this sheet is this below

Sub Tuesday_Class_Sizer()
If Range("A1").Value = "0" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("750").EntireRow.Hidden = False
Rows("1:748").EntireRow.Hidden = True
ElseIf Range("A1").Value = "1" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:32").EntireRow.Hidden = False
Rows("33:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "2" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:63").EntireRow.Hidden = False
Rows("64:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "3" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:94").EntireRow.Hidden = False
Rows("95:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "4" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:125").EntireRow.Hidden = False
Rows("126:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "5" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:156").EntireRow.Hidden = False
Rows("157:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "6" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:187").EntireRow.Hidden = False
Rows("188:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "7" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:218").EntireRow.Hidden = False
Rows("219:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "8" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:249").EntireRow.Hidden = False
Rows("250:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "9" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:280").EntireRow.Hidden = False
Rows("281:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "10" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:311").EntireRow.Hidden = False
Rows("312:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "11" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:342").EntireRow.Hidden = False
Rows("343:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "12" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:373").EntireRow.Hidden = False
Rows("374:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "13" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:404").EntireRow.Hidden = False
Rows("405:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "14" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:435").EntireRow.Hidden = False
Rows("436:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "15" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:466").EntireRow.Hidden = False
Rows("467:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "16" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:497").EntireRow.Hidden = False
Rows("498:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "17" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:528").EntireRow.Hidden = False
Rows("529:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "18" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:559").EntireRow.Hidden = False
Rows("560:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "19" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:590").EntireRow.Hidden = False
Rows("591:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "20" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:621").EntireRow.Hidden = False
Rows("622:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "21" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:652").EntireRow.Hidden = False
Rows("653:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "22" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:683").EntireRow.Hidden = False
Rows("684:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "23" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:714").EntireRow.Hidden = False
Rows("715:750").EntireRow.Hidden = True
ElseIf Range("A1").Value = "24" Then
Sheets("Tuesday").Unprotect Password:="123"
Rows("1:750").EntireRow.Hidden = False
Rows("2:745").EntireRow.Hidden = False
Rows("750").EntireRow.Hidden = True

End If
Sheets("Tuesday").Protect Password:="123"
End Sub

 

[Joe4]

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.