i am trying to automatically hide or show rows depending on cell value in column O

[bunny1580]

i used this vba but it crashed my excel anyone who can thing of something i first used autofilter but then everytime i type somthing and move to a other cell excel has to load for 2 sec and it is not very handy to have it load 2 sec everytime so i went to vba but i cant get it to work because it has to work smoothly?


Private Sub Worksheet_Calculate()
Dim cell As Range
Dim rng As Range
Set rng = Me.Range("O:O") ' Monitors changes in column O

For Each cell In rng
If cell.Value = "HIDE" Then
cell.EntireRow.Hidden = True
ElseIf cell.Value = "SHOW" Then
cell.EntireRow.Hidden = False
End If
Next cell
End Sub

[#2]

@bunny1580 

Does column O contain formulas, or are the cells edited directly by the user?

[#3]

it contains a formula

[#4]

@bunny1580 

Which cell(s) do the formula refer to?

It might help if you could attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.

[#5]

@Hans Vogelaar 

https://docs.google.com/spreadsheets/d/1csG7hx1BlqjZ1D_Hp93tT_iG9ChD4MuP/edit?usp=share_link&ouid=10... its at O99 where i am testing it if you open it you may need to spam esc or excel crashes and in case you need it pasword is 123

[#6]

@bunny1580 

I can avoid the crash by pressing Esc, but after that Excel keeps on running into problems. I fear it's too complicated, sorry.