Hide Rows based on another cell value changing

[Andy booth]

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>

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]

 

[Snakehips]

Andy,

Do you have a defined very last row of the data that might be hidden or will that last row vary?

 

[Andy booth]

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?

 

[Snakehips]

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 ??

 

[Snakehips]

Andy,

Try this...

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.