fix a macro

Hi @bill gras ,

Thanks for reaching out.

I looked at your macro and the issue comes from two things: a small typo in your code (Criterial should be Criteria1) and the fact that Excel blocks macros from running if there are locked cells. Even though you only locked C2, that’s enough to cause the “Can’t execute code in break mode” message.

Temporarily unprotect the sheet while the macro runs, apply the filter, and then protect it again. Here’s a version you can use:

Sub Hide1Rows()
    Dim WS As Worksheet
    Set WS = Worksheets("Sit")

    ' Allow macro to work even with locked cells
    WS.Unprotect Password:=""

    ' Clear any existing filters
    If WS.AutoFilterMode Then WS.AutoFilterMode = False

    ' Filter column KL: hide rows with 1, show everything else
    WS.Columns("KL").AutoFilter Field:=1, Criteria1:="<>1"

    ' Protect sheet again but allow macros to run
    WS.Protect Password:="", UserInterfaceOnly:=True
End Sub

Hope this helps! If my answer was helpful - kindly follow the instructions here so others with the same problem can benefit as well.

It seems like you're encountering an issue with your macro due to the worksheet being in break mode. This can happen if there is an error in the code or if the macro was interrupted. Here are a few suggestions to help you fix your macro:

1. Correct the Syntax: Your macro has a small typo in the Criteria parameter. It should be Criteria instead of Criterial. Here’s the corrected version:

      Sub Hide1Rows()
          Dim WS As Worksheet
          Set WS = Worksheets("Sit")
          WS.AutoFilterMode = False
          WS.Columns("KL").AutoFilter Field:=1, Criteria:="<>1"
      End Sub
   

2. Check for Break Mode: Make sure that your Excel is not in break mode. You can reset it by clicking on the "Reset" button in the VBA editor or by pressing Ctrl + Break to stop the execution of the macro.
3. Unlocking Cell C2: If the macro still doesn’t work, try unlocking cell C2 temporarily to see if that resolves the issue. If it does, you may need to adjust your macro to ensure it can run with the cell locked.
4. Testing the Macro: After making these changes, try running the macro again. Ensure that there are no other issues in the worksheet that might cause the macro to fail.

If you continue to have problems, consider checking for any other potential errors in your worksheet or the macro itself.

References:

• How to loop through a list of data on a worksheet by using macros in Excel
• Getting started with VBA in Office
• Can't perform operation because the project is protected
• SolverSolve Function