Hide/Unhide Rows based on Data validation value

akshay0880

New Member
Joined
Jul 11, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Screenshot 2021-09-02 172750.jpg
    Screenshot 2021-09-02 172750.jpg
    161.8 KB · Views: 29

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
try this
VBA Code:
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
 
Upvote 0
try this
VBA Code:
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?
 

Attachments

  • Screenshot2.jpg
    Screenshot2.jpg
    247.8 KB · Views: 11
Upvote 0
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"
 
Upvote 0
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.

VBA Code:
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?
 
Upvote 0
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.

VBA Code:
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)
 
Upvote 0
Hello @rpaulson
Attached screenshot with error.

Thank you again.
 

Attachments

  • Screenshot 2021-09-03 225313.jpg
    Screenshot 2021-09-03 225313.jpg
    116.7 KB · Views: 10
Upvote 0
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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