Delete Rows Based on Drop-Down

[Edavis1999]

Hi guys!

I've made this so that when people toggle the drop-down in B5 it populates either "Item" based on No and Items 1-6 based on Yes. What I want to be able to do is have the A8-A12 and so on - collapse when someone replies No rather than having blank fields.

Is this something I will need a macro for, and if so, can someone help me through it?

Ty~~

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, use code tags to post the code you are currently using to populate either "Item" based on No and Items 1-6 based on Yes.

 

[Edavis1999]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, use code tags to post the code you are currently using to populate either "Item" based on No and Items 1-6 based on Yes.

Wow, this is a fantastic add-on!

Interactive expense form.xlsm
	A	B	C
1	Expenses form
2	Name:
3	Date:	23/08/2022
4	Are you making more than one claim?
5	Select Yes or No	Yes
6	Has prior consent been granted and by who?
7	Item 1		--
8	Item 2
9	Item 3
10	Item 4
11	Item 5
12	Item 6
13	Type of expense (meal, drinks, travel, hotel etc.):
14	Item 1
15	Item 2
16	Item 3
17	Item 4
18	Item 5
19	Item 6
20	Reason for event:
21	Item 1
22	Item 2
23	Item 3
24	Item 4
25	Item 5
26	Item 6
27	Evidence (physical receipt, digital receipt, bank statement etc.):
28	Item 1
29	Item 2
30	Item 3
31	Item 4
32	Item 5
33	Item 6
34	Cost of each item (£)
35	Item 1
36	Item 2
37	Item 3
38	Item 4
39	Item 5
40	Item 6
41	Amount Claimed:	£0.00
Sheet2

Cell Formulas
Range	Formula
B3	B3	=TODAY()
A7:A12	A7	=IFERROR(INDIRECT("List"&B5),"")
A14:A19	A14	=IFERROR(INDIRECT("List"&B5),"")
A21:A26	A21	=IFERROR(INDIRECT("List"&B5),"")
A28:A33	A28	=IFERROR(INDIRECT("List"&B5),"")
A35:A40	A35	=IFERROR(INDIRECT("List"&B5),"")
B41	B41	=SUM(B35:B40)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=Listt
B7:B12	List	Yes,No
B28:B33	List	Physical reciept,Digital reciept,Bank stament,Copy of reciept
C7	List	--,ADC,JXD
B14	List	Other,Hotel,Train,Flight,Taxi,Meal,Drinks,Covid Test,Subscription
B15:B19	List	Hotel,Train,Flight,Taxi,Meal,Drinks,Covid Test,Subscription
B21	List	Other,Arbitration,Client Meeting,Court,Business Development
B22:B26	List	Arbitration,Client Meeting,Court,Business Development

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection of "Yes" or "No" in B5.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B5" Then Exit Sub
    Application.ScreenUpdating = False
    Dim x As Long, y As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
    Select Case Target.Value
        Case "No"
            For x = 7 To lRow Step 7
                Range("A" & x) = "Item"
                Range("A" & x + 1).Resize(5).ClearContents
                Rows(x + 1 & ":" & x + 5).Hidden = True
            Next x
        Case "Yes"
            For x = 7 To lRow Step 7
                Rows(x + 1 & ":" & x + 5).Hidden = False
                For y = 0 To 5
                    Range("A" & x + y) = "Item " & y + 1
                Next y
            Next x
    End Select
End Sub

 

[Edavis1999]

Absolutely smashed it! Thank you!

 

[mumps]

You are very welcome.