hiding rows depending on drop down list

pawwwd

New Member
Joined
Aug 5, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
im looking for an easy way VBA to hide specific rows in excell, got three choices in a drop down list and depending of what I choose i want three different set of rows to be hidden.

If i choose "AM" i want to hide rows 30:48
if i choose "PM" i want to hide rows 16:29 and 41:48
if i choose "NS" i want to hide rows 16:40
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Right click the sheet tab and select "View Code". Paste in the below. Change B2 to the cell that holds your dropdown list.,

VBA Code:
'************************************************************
'*                                                          *
'*  If i choose "AM" i want to hide rows 30:48              *
'*  if i choose "PM" i want to hide rows 16:29 and 41:48    *
'*  if i choose "NS" i want to hide rows 16:40              *
'*                                                          *
'*  Author: dave3009                                        *
'*  Date: 2022-09-27                                        *
'*                                                          *
'************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False                        'Turn off event listening
If Not Intersect(Target, Range("B2")) Is Nothing Then   'Check if the dropdown cell has changed
    Rows("16:48").Hidden = False                        'Unhide all the rows
    Select Case Target.Value                            'Select the case for the change and hide
        Case Is = "AM"                                  ' the relevant rows
            Rows("30:48").Hidden = True
        Case Is = "PM"
            Rows("16:29").Hidden = True
            Rows("41:48").Hidden = True
        Case Is = "NS"
            Rows("16:40").Hidden = True
    End Select
End If
Application.EnableEvents = True                         'Turn on event listening
End Sub
 
Upvote 0
Solution

pawwwd

New Member
Joined
Aug 5, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
1664243979348.png
1664244036601.png
 
Upvote 0

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
You did not follow the instructions.
 
Upvote 0

Forum statistics

Threads
1,186,846
Messages
5,960,170
Members
438,464
Latest member
rangers277

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
Top