How to hide rows based on dropdownlist

0 votes

I got to know to hide basic rows as screenshot: When I select 1 it hides rows 9-14 and so on.

enter image description here

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set Triggercell = Range("D2")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
        If Triggercell.Value = 1 Then
            Rows("9:14").Hidden = False
            Rows("11:14").Hidden = True
        ElseIf Triggercell.Value = 2 Then
            Rows("9:14").Hidden = False
            Rows("13:14").Hidden = True
        ElseIf Triggercell.Value = 3 Then
            Rows("9:14").Hidden = False
        End If
End If
End Sub

But what if I have multiple columns with the same name? How can I conceal the rows of screenshots below? Due to the lack of a distinct cell like the one above D2. If I choose options 1, 2, and 3, I want to hide rows 27 through 36 and then reveal those same rows when I choose option 3.

enter image description here

Jan 8 in Others by Kithuzzz
• 28,700 points
43 views

1 answer to this question.

0 votes

No need of any variable (besides Target). Please, use the next adapted code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.address = "$D$2" Then
        If Target.Value = 1 Then
            Me.rows("9:14").Hidden = False
            Me.rows("11:14").Hidden = True
        ElseIf Target.Value = 2 Then
            Me.rows("9:14").Hidden = False
            Me.rows("13:14").Hidden = True
        ElseIf Target.Value = 3 Then
            Me.rows("9:14").Hidden = False
        End If
 End If
End Sub
answered Jan 8 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
0 answers

Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 28,700 points
614 views
0 votes
1 answer

Excel Function to Exclude rows based on certain values

One method is to combine the FILTER() ...READ MORE

answered Jan 23 in Others by narikkadan
• 53,160 points
42 views
0 votes
1 answer

How to expend the code to transfer data from one spreadsheet to another based on multiple criteria

 The progress bar is unnecessary. Option Explicit Sub VTest2() ...READ MORE

answered Jan 29 in Others by narikkadan
• 53,160 points
31 views
0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered 2 days ago in Others by narikkadan
• 53,160 points
25 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
530 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,408 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
294 views
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20 in Others by narikkadan
• 53,160 points
36 views
0 votes
1 answer

Excel function to dynamically SUM UP data based on matching rows and columns

Excel 365 for MAC should have the BYCOL function, Given: Your ...READ MORE

answered Jan 21 in Others by narikkadan
• 53,160 points
54 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP