CODEWITHSUNDEEP
vba
Kevin Billings
Kevin Billings

Reputation: 77

How to run macro when cell value changed by formula results for code that hides/unhides rows based on formula result?

I have a worksheet that hides or unhides rows based on the value as a cell. I end up getting a run time error that reads:

-2147417848 (80010108): Method 'Hidden' of object 'Range' failed, with a yellow arrow pointing at the first Rows equation

and then another run-time error '1004': Unable to set the hidden property of the range class.

Option Explicit

Private Sub Worksheet_Calculate()
  Rows("1:26").EntireRow.Hidden = [a1] = 0
  Rows("27:52").EntireRow.Hidden = [a27] = 0
  Rows("53:78").EntireRow.Hidden = [a53] = 0
  Rows("79:104").EntireRow.Hidden = [a79] = 0
  Rows("105:130").EntireRow.Hidden = [a105] = 0
  Rows("131:156").EntireRow.Hidden = [a131] = 0
  Rows("157:182").EntireRow.Hidden = [a157] = 0
  Rows("183:208").EntireRow.Hidden = [a183] = 0
  Rows("209:234").EntireRow.Hidden = [a209] = 0

End Sub

Upvotes: 0

Views: 91

Answers (1)

Tim Williams
Tim Williams

Reputation: 166540

Should look like this:

Private Sub Worksheet_Calculate() 
    Dim v
    v = Me.Range("A1").Value
    Me.Range("1:26").EntireRow.Hidden = (v = 0)
End Sub

I don't know if you need to handle potential error values in the A1 formula result.

EDIT: try this - it looks like your worksheet is entering a loop where the hide/show is re-triggering another calculation, etc, etc

Private Sub Worksheet_Calculate()

  On Error GoTo haveError

  Application.EnableEvents = False

  Me.Range("1:26").EntireRow.Hidden = [a1] = 0
  Me.Range("27:52").EntireRow.Hidden = [a27] = 0
  Me.Range("53:78").EntireRow.Hidden = [a53] = 0
  Me.Range("79:104").EntireRow.Hidden = [a79] = 0
  Me.Range("105:130").EntireRow.Hidden = [a105] = 0
  Me.Range("131:156").EntireRow.Hidden = [a131] = 0
  Me.Range("157:182").EntireRow.Hidden = [a157] = 0
  Me.Range("183:208").EntireRow.Hidden = [a183] = 0
  Me.Range("209:234").EntireRow.Hidden = [a209] = 0

haveError:
  Application.EnableEvents = True

End Sub

Upvotes: 0

Related Questions