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]

Salam,
Why dont you use Autofilter instead??? better and easier.
Something like...

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]

Salam,
Why dont you use Autofilter instead??? better and easier.
Something like...

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]

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]

How about

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]

How about

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]

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]

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]

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]

@fadee2
Vlookup is not a volatile function.