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, 2023 in Others by Kithuzzz
• 38,020 points
802 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, 2023 by narikkadan
• 63,720 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
• 38,020 points
2,611 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, 2023 in Others by narikkadan
• 63,720 points
7,469 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, 2023 in Others by narikkadan
• 63,720 points
386 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 Feb 4, 2023 in Others by narikkadan
• 63,720 points
873 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
1,098 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
3,517 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
• 23,260 points
821 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,690 points
982 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, 2023 in Others by narikkadan
• 63,720 points
551 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, 2023 in Others by narikkadan
• 63,720 points
808 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