Sveral hide conditions

[rekoj]

Hi there,

recently started to work with VBA, so still it's a new world for me.

I've created a really easy formula for hiding severall rows, and am trying to insert anotherone, which would work the same, but would be independed from the last one. But somehow it only makes one formula to work (first). Maybe anyone can say what have I done wrong?


Private Sub Worksheet_Calculate()
If Range("R1").Value > 0 Then
Columns("J:R").Hidden = True
Else
Columns("J:R").Hidden = False
End If

End Sub



Private Sub Worksheet_Calculation1()
If Range("V1").Value > 2 Then
Columns("S:T").Hidden = True
Else
Columns("S:T").Hidden = False
End If

End Sub

[RHCPgergo]

You are using an event macro and as far as I know you can only have one those (of each kind) in a single worksheet. The syntax has to be exactly the same, so you can't change the name of the sub to something else. (The WorkSheet_Calculate macro runs each time the worksheet fomulas are recalculated.)

Put everything in one sub. Something like this should work:

Please Login or Register  to view this content.

[haripopuri]

First one is called 'event' not function. Event syntax should be exactly same as the first one, you cannot and should not modify in order to work with it.

[rekoj]

It lookes so easy when someone else is doing that... it does work now.. Thanks!

[rekoj]

Ok, but still when I changed that in second "If" the rows would disapear, not the columns any more, my excel begun to stuck, and formula not working anymore. Had I missed here anything?

Private Sub Worksheet_Calculate()

If Range("R1").Value > 0 Then
Columns("J:R").Hidden = True
Else
Columns("J:R").Hidden = False
End If

If Range("V1").Value > 2 Then
Rows("45:47").Hidden = True
Else
Rows("45:47").Hidden = False
End If

End Sub

[RHCPgergo]

Not sure why that would happen...

[rekoj]

Is it possible that there is some kind of rule which prevents the VBA to work when I am trying to hide rows together with columns in one Sub? Perhaps there is any other way I can overcome this case, using any other formulas?

[RHCPgergo]

I can't reproduce the error you get, it works for me. Can you attach a workbook where it doesn't work?

[rekoj]

I actually works when I do that in new workbook, without all the information I've already made. Trying to enter all the info from begining, but starting from VBA coding first, and it seems that it will work... I don't know why actually.. But thanks for help!