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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
Salam,
Why dont you use Autofilter instead??? better and easier.
Something like...

VBA Code:
If ActiveSheet.AutoFilterMode = False Then
    Sheets("sheet5").Range("a1:f25").AutoFilter Field:=5, Criteria1:="<>Hide"
Else
    ActiveSheet.AutoFilterMode = False
End If

Where rows are starting from 1 and ending at 25 of columns A to F.
Field5 is the column containing your "Hide" values.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Salam,
Why dont you use Autofilter instead??? better and easier.
Something like...

VBA Code:
If ActiveSheet.AutoFilterMode = False Then
    Sheets("sheet5").Range("a1:f25").AutoFilter Field:=5, Criteria1:="<>Hide"
Else
    ActiveSheet.AutoFilterMode = False
End If

Where rows are starting from 1 and ending at 25 of columns A to F.
Field5 is the column containing your "Hide" values.
Wsalaam I am completely new so don't know much about the VBA coding. I tried pasting this and changing sheet5 to sheet1 but its giving me an error. I tried putting the sub and end sub as well but no luck. Is there a specific code that would work?
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
what is the error??
because what this cide is doing is basically...

1. looks for autofilter in sheet5
2. if it is not active then activates it,
3. then filter the data (a1 to f25), based on the value present in the cells in column 5.
4. if cells in column 5 contains "hide", it hides the row

(Field 5 is the fifth column on your worksheet, that contains "hide", in certain cells.)

can you share the worksheet in question.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E2:E6")) Is Nothing Then
      Rows(Target.Row).Offset(8).Hidden = Target.Value = "Hide"
   End If
End Sub
This needs to go in the relevant sheet module.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E2:E6")) Is Nothing Then
      Rows(Target.Row).Offset(8).Hidden = Target.Value = "Hide"
   End If
End Sub
This needs to go in the relevant sheet module.
Hi this works. However, is there a way for me to use a VBA that is dependent on the outcome of E10:E14 as opposed to E2:E6?
Just a little background, the example I used is basically a much simpler version of what I am trying to achieve.
So I am running a vlookup with an iferror, thus if the item can't be found on the vlookup it returns "Hide" as a result (This can be changed to anything else if needed). The problem is that if it shows 25 rows of a possible 100 and then I change the data where it vlookups 40 items, only the first 25 of these are showing. Thus, I need something that can update the rows automatically but there isn't a cell such as E2:E6 that it is pulling from but is rather from an iferror vlookup formula.

I hope this makes sense, your help thus far has been appreciated, sorry for misunderstanding.
Please assist based on the explanation above if possible.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

what is the error??
because what this cide is doing is basically...

1. looks for autofilter in sheet5
2. if it is not active then activates it,
3. then filter the data (a1 to f25), based on the value present in the cells in column 5.
4. if cells in column 5 contains "hide", it hides the row

(Field 5 is the fifth column on your worksheet, that contains "hide", in certain cells.)

can you share the worksheet in question?
Hi Fadee, not sure how to attach the file. Is there an alternative way to send it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
For future reference, it is better to state exactly what you want from the word go, rather than trying to "simplify" your question.
In this instance you have 2 people who have spent their free time trying to help you do something that is not what you actually need.

The only way you can automate this is with a calculation event, but that will trigger every time any cell in the sheet recalculates, which could seriously slow down your workbook.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
The only way you can automate this is with a calculation event, but that will trigger every time any cell in the sheet recalculates, which could seriously slow down your workbook

not to mention, vlookup itself is a volatile function and will slow it down.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
@fadee2
Vlookup is not a volatile function.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,078
Members
415,875
Latest member
Tarali

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