Hide and unhide rows updated based on cell data

[Hamza Oza]

Hi all,

I am trying to create an Excel that allows me to hide/unhide rows based on cell data based on a formula. It works the first time but then when the data changes it does not unhide/hide the previous data unless I go to the code and refresh after the new data has been selected. I have included a basic example: E10 is pulling from E2, E11 from E3, E12 from E4, E13 from E5 and E14 from E6. Let's say I put "Hide" in cells E2:E6 which then pulls through in cells E10:E14 through a formula and put the below VBA:

Sub HRows()
BeginRow = 10
EndRow = 14
ChkCol = 5

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

It hides the rows 10-14 correctly. However, when I remove the word "Hide" from let's say cell E2 and E3 it does not unhide rows 10 and 11 automatically but rather only when I "view code" and refresh (F5)

Is there any way to update the VBA to update automatically? I am very new to VBA coding.

 

[fadee2]

but i thought it was, since it involves array and logically it shall recalculate if anything in that array is changed and/or if the sheet is closed and reopened. dont you think??

 

[Fluff]

All formulae will recalculate if a cell it looks at changes, but that has nothing to do with being volatile.
A volatile function will recalculate whenever any cell in any open workbook changes.

 

[fadee2]

All formulae will recalculate if a cell it looks at changes, but that has nothing to do with being volatile.
A volatile function will recalculate whenever any cell in any open workbook changes.

ahannn..... ok...
gained some more knowledge..... information is always good.. ?

thanks fluff....

 

[Fluff]

 

[Brent R Janetzki]

Hi I have a similar question to this. I am trying to hide rows base on a a specific value selcted in a drop down list. For example I have a drop down list of Yes and No in Cell C42. When I click on YesI am wanting rows 45 to 47 to remain but when I click on No I wnant them to hide. I've spent hours trying to work this out but I keep getting Syntax Errors and Code 438 for some reason. Talk about frustrating. Any help would be appreciated.

 

[Fluff]

Please start a thread of your own, rather than "hijacking" another members thread. Thanks