Hide and unhide rows updated based on cell data

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
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??
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
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....
 

Brent R Janetzki

New Member
Joined
Mar 11, 2017
Messages
18
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Please start a thread of your own, rather than "hijacking" another members thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,118
Messages
5,622,831
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top