Hide and Unhide rows based on dropdown list

rahiljaved

New Member
Joined
Aug 18, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi
I have tried various codes but either it just hide and never unhide when the value change from dropdown or either it doesn't work.
Situation: I have a dropdown list (Cell B32) have 5 different categories in that and based on that list my data populates in table (A52:D100) using Index and match function at the table. In last row of the data (row 101) I have subtotal of the table. Now, I when I select B32, I want the data to be changed which it does for example: when the cell value shows "Category A" in cell B32, the data range from A52:D60 shows but my susbtotal stays at row 101 down below. I want the rest of empty rows from A61:D100 hidden and when I select "Category B" in Cell B32, it shows data from A52:D54 and again the subtotal rows stays at 101 which I want to come up to row 55 by hiding rest of empty rows. Please let me know if you have a solution.

Thanks in advance!
 

Attachments

  • a.PNG
    a.PNG
    10.2 KB · Views: 6
  • b.PNG
    b.PNG
    19.6 KB · Views: 6

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

rahiljaved

New Member
Joined
Aug 18, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
New Microsoft Excel Worksheet.xlsm
ABCDEFGHIJKLMNOPQRS
1Selectddata200data
2data300dd
3data500d
4DataValuedata600
5d4data700
6d5data500
7d2data600
8d1data400
9  data800
10  data900
11  dd60
12  d4
13  d5
14  d2
15  d1
16  dd50
17  dd40
18  dd30
19Total12dd20
20dd40
21dd10
22
23
24
25
26
Sheet1
Cell Formulas
RangeFormula
A5:A18A5=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($M$1:$M$21)),ROW()-4),1),"")
B5:B18B5=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($N$1:$N$21)),ROW()-4),2),"")
B19B19=SUBTOTAL(9,B5:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
B1List=$R$1:$R$3
 

rahiljaved

New Member
Joined
Aug 18, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
New Microsoft Excel Worksheet.xlsm
ABCDEFGHIJKLMNOPQRS
1Selectddata200data
2data300dd
3data500d
4DataValuedata600
5d4data700
6d5data500
7d2data600
8d1data400
9  data800
10  data900
11  dd60
12  d4
13  d5
14  d2
15  d1
16  dd50
17  dd40
18  dd30
19Total12dd20
20dd40
21dd10
22
23
24
25
26
Sheet1
Cell Formulas
RangeFormula
A5:A18A5=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($M$1:$M$21)),ROW()-4),1),"")
B5:B18B5=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($N$1:$N$21)),ROW()-4),2),"")
B19B19=SUBTOTAL(9,B5:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
B1List=$R$1:$R$3
This is a sample data that I have created not the actual sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Hide and Unhide rows based on dropdown list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

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