Hide/Unhide Rows based on Data validation value

[akshay0880]

Hi Guys,

I am trying to do something rather simple but I am not sure how to do this.

So basically I just want the workbook to only show the week number that I select from the dropdown list and the rest should be hidden.
Attached is a screenshot for better understanding.

Dropdown is in C2 and the data starts in Row 7: 141.
Basically it should only show what I select from the dropdown.

Thanks in advance.

 

[rpaulson]

try this

Sub Hide_them()
Dim foundRng As Range

On Error GoTo 99
Set foundRng = Range("B:B").Find([C2])
r = foundRng.Row

Rows("7:500").Hidden = True
Range("A" & r & ":A" & r + 7).EntireRow.Hidden = False

Exit Sub

99 MsgBox ("Week not Found")

End Sub

 

[akshay0880]

try this

Sub Hide_them()
Dim foundRng As Range

On Error GoTo 99
Set foundRng = Range("B:B").Find([C2])
r = foundRng.Row

Rows("7:500").Hidden = True
Range("A" & r & ":A" & r + 7).EntireRow.Hidden = False

Exit Sub

99 MsgBox ("Week not Found")

End Sub

Hi @rpaulson , Thanks for the response. The solution provided does not appear to be working.
I pasted the code on the sheet's code but nothing happens when I choose the dropdown in C2.

Maybe I am doing something wrong?

 

[akshay0880]

Hi @rpaulson , Thanks for the response. The solution provided does not appear to be working.
I pasted the code on the sheet's code but nothing happens when I choose the dropdown in C2.

Maybe I am doing something wrong?

Update, I also tried to assign the mentioned code to a button and it does not work but it does show the pop up of "Week not Found"

 

[rpaulson]

to get it to run when you select item from drop down here is what you need to do

Right click on the worksheet name on the bottom. select view code.

paste this in.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("C2")) Is Nothing Then Call Hide_them
End Sub

I assumed your headings "Week 36 Week 37 Week 38" were in column B

what column are they in?

 

[akshay0880]

to get it to run when you select item from drop down here is what you need to do

Right click on the worksheet name on the bottom. select view code.

paste this in.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("C2")) Is Nothing Then Call Hide_them
End Sub

I assumed your headings "Week 36 Week 37 Week 38" were in column B

what column are they in?

Hi, So my worksheet is called E10, i right clicked>View Code and pasted this:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then Call Hide_them
End Sub
____________________________________________________________________________________________
Sub Hide_them()
Dim foundRng As Range

On Error GoTo 99
Set foundRng = Range("B:B").Find([C2])
r = foundRng.Row

Rows("7:500").Hidden = True
Range("A" & r & ":A" & r + 7).EntireRow.Hidden = False

Exit Sub

99 MsgBox ("Week not Found")

End Sub


After this, I am choosing from the dropdown C2 (week 37 for example) but nothing happens
All of my Week 36, Week 37, Week 38.... is in column B

on the VBA screen, where the code is, when I try to click on play/continue icon at the top, I have this error which says Compile error: Variable not defined.

Not sure if that is the cause of it but, the source of my dropdown list is in another sheet (same workbook)

 

[akshay0880]

Hello @rpaulson
Attached screenshot with error.

Thank you again.

 

[rpaulson]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then Call Hide_them
End Sub

Sub Hide_them()
Dim foundRng As Range
Dim r As Integer
On Error GoTo 99
Set foundRng = Range("B:B").Find([C2])
r = foundRng.Row

Rows("7:500").Hidden = True
Range("A" & r & ":A" & r + 7).EntireRow.Hidden = False

Exit Sub

99 MsgBox ("Week not Found")

End Sub