VBA - Runtime error with END IF

Question

I am relatively new to VBA. And when I was working on a worksheet I created a code that automatically hides/unhides rows based on a condition in a column row (0 unhide/1 hide). This relatively easy macro worked well until I added a different sheet. As there are no macros in this sheet I dont think it is related. But now everytime it gives a runtime error on the END IF function and I don't know how to solve it. There is probably a simple solution, but I cannot find it.

Here is the code:

    Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long, c As Range
    Application.EnableEvents = False
    LastRow = Cells(Cells.Rows.Count, "BA").End(xlUp).Row
        On Error Resume Next
        For Each c In Range("BA34:BA56,BA73:BA74,BA76:BA107")
        If c.Value = 1 Then
            c.EntireRow.Hidden = True
        ElseIf c.Value = 0 Then
            c.EntireRow.Hidden = False
    End If
    Next
    On Error GoTo 0
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

Probably not the source of your error but you could encompass that whole If ... Else ... End If as c.EntireRow.Hidden = CBool(c.Value) — user4039065
– user4039065, Commented Jul 10, 2016 at 7:34
Remove or comment out the On Error Resume Next and apply a parent worksheet reference to the range object. The worksheet may have volatile formulas that are triggering a calculation event due to changes on the other worksheet. — user4039065
– user4039065, Commented Jul 10, 2016 at 7:38
Does this have to run if the parent worksheet is not active? Could changes in another worksheet affect column BA in this worksheet? — user4039065
– user4039065, Commented Jul 10, 2016 at 7:49

score 1 · Accepted Answer · 2016-07-10 07:58:13Z

1

Remove or comment out the On Error Resume Next if you want more complete debugging information. It should not be necessary in an event macro that runs as often as Worksheet_Calculate.

Apply a parent worksheet reference to the range object. The worksheet may have volatile formulas that are triggering a calculation event due to changes on the other worksheet.

I've added a wide condition so that the code will only run if the parent worksheet holds the ActiveSheet property. The worksheet may have volatile¹ formulas that are triggering a calculation event due to changes on the other worksheet (or even another workbook).

LastRow does nothing after its assignment (which is problematic) so I removed it.

I simplified the Range.Hidden property to a single line.

Private Sub Worksheet_Calculate()
    If Me.Name = Activesheet.Name Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim c As Range
        For Each c In Me.Range("BA34:BA56,BA73:BA74,BA76:BA107")
            c.EntireRow.Hidden = CBool(c.Value2)
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}

edited Jul 10, 2016 at 7:58

answered Jul 10, 2016 at 7:52

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

AlmarM Over a year ago

Thanks for your response. Indeed the initial error was only runtime error and it took a long time for the macro to work. The Sheet is just "Sheet1". Replacing it with your suggestion it says runtime error "1004". Thank you very much for your help and quick response.

AlmarM Over a year ago

Just to finish the thread. I solved it based on a combination of the code you wrote and mine, as the 0 should be the trigger to unhide as well. I guess the error line caused the issue.

user4039065 Over a year ago

I don't actually understand the problem since CBool(0) resolves to False. In almost any programming language, False equates to zero and in boolean terms, anything that is not false is true; e.g. #define False 0: #define True Not False.

AlmarM · Accepted Answer · 2016-07-13 06:38:20Z

Just to finish the thread. I solved it based on a combination of the code you wrote and mine, as the 0 should be the trigger to unhide as well. I guess the error line caused the issue.

    Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long, c As Range
    Application.EnableEvents = False
    LastRow = Cells(Cells.Rows.Count, "BA").End(xlUp).Row
    On Error Resume Next
    For Each c In Range("BA34:BA56,BA73:BA74,BA76:BA107")
       If c.Value = 1 Then
            c.EntireRow.Hidden = True
        ElseIf c.Value = 0 Then
            c.EntireRow.Hidden = False
        End If
    Next
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

Collectives™ on Stack Overflow

VBA - Runtime error with END IF

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related