Hide Rows based on another cell value changing

Andy booth

New Member
Joined
Feb 12, 2013
Messages
7
Hi, I don't think what I am trying to do is too complex but I am getting stumped with it. What I have is 2 sheets in a workbook. Sheet 1 has cells with a unique id number and a yes/no/not relevant selection box. Sheet 2 has a lot more data but the data is grouped by the unique id number from sheet 1. So id 1 could have 5 rows of data on sheet 2 and id 2 could have 15 rows and id 3 8 rows and so on. On sheet 2 the ids are in column B. What I want is when the user selects no or not relevant the associated rows on sheets 2 get hidden and when the user select yes the rows become unhidden. I have put together the following code but it is only hiding the row where the id is present in sheet 2. I want it to hide all the rows from the position of the id to the next id. Can this be done, without having to name over 100 ranges? Below is the code I have so far and an example of sheet 2. So if id 1 =no then first 3 rows should be hidden, and if id 3=no then 8 rows should be hidden. Any help would be great thanks. Andy
1
Excel
word
PP
SP
Reason
reason
reason
reason
reason
reason
reason
reason
2
Apple
Orange
Pear
Lemon
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
3
Civic
Golf
Yaris
Colt
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
reason
4
Horse
Dog
Cat
Sheep
reason
reason
reason
reason
reason

<tbody>
</tbody>
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rcounter As String
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Rng As Range
Set Sheet1 = ActiveWorkbook.Sheets("Library Review")
Set Sheet2 = ActiveWorkbook.Sheets("Assessment")
Set Rng = Sheet2.Range("B11:B51")
Rcounter = Application.WorksheetFunction.Match(Sheet1.Cells(Target.Row, 1), Sheet2.Range("B11:B51"), 0)
MsgBox (Rcounter)
    If Target.Value = "No" Then
        Rng.Rows(Rcounter).EntireRow.Hidden = True
    ElseIf Target.Value = "Yes" Then
       Rng.Rows(Rcounter).EntireRow.Hidden = False
    End If
    
End Sub
[CODE]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Andy,

Do you have a defined very last row of the data that might be hidden or will that last row vary?
 
Upvote 0
Hi, No I don't but this can be added. I presume this would mean the code has a specific end point to go to rather than basically nothing which I have at present.
Andy,

Do you have a defined very last row of the data that might be hidden or will that last row vary?
 
Upvote 0
I wondered if the data range was limited given that you have a specific range, B11:B51 in your existing code?
The issue is that the code I would normally use to find the last populated row will not recognise that row if it happens to be hidden. So if the data associated with your last ID number is hidden and we need to get it back visible the code needs to know when to stop.

I have an idea that may sort it. I will post shortly and you can pass comment.
Just confirm that your first Id is in row 11 ??
 
Upvote 0
Andy,

Try this...

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rcounter As String
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim lastrow As Integer
'*******************
'***** Assuming Target cell with Yes / no/ na in sheet1 is in column B (2)  ????? edit to in line below suit if not
'*******************
If Not Target.Column = 2 Then Exit Sub   '***********
Set Sheet1 = ActiveWorkbook.Sheets("Library Review")
Set Sheet2 = ActiveWorkbook.Sheets("Assessment")
Application.ScreenUpdating = False
lastrow = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row
Rcounter = Application.WorksheetFunction.Match(Sheet1.Cells(Target.Row, 1), Sheet2.Range("B1:B20000"), 0)
Hide = True
If Target.Value = "Yes" Then Hide = False
With Sheet2
Do Until Not .Cells(Rcounter, 2) = "" And Not .Cells(Rcounter, 2) = Sheet1.Cells(Target.Row, 1)
.Rows(Rcounter).EntireRow.Hidden = Hide
Rcounter = Rcounter + 1
If Hide = False Then
If .Rows(Rcounter).EntireRow.Hidden = False Then Exit Do
Else
If Rcounter > lastrow Then Exit Do
End If
Loop
End With

Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top