Share via

Collapsible Rows via VBA

Anonymous
2023-03-29T20:45:27+00:00

Link to my example on excel:

ExampleforCommunityHelp.xlsx

First Problem:

I am wanting to have Heading's 3, 4, 5, and 6 collapsible to make it easier form information to be shown. I want them to start collapsed and upon "YES" indication in the J column they open.

Rows that always need to be visible:

1-13, 17, 23, 28, 33-end

Read the information from cell J13

BLANK = Closed

YES = Open rows - 14-16 

      Do not open any other rows

Read the information from cell J17
BLANK = Closed

YES = Open rows - 18-22

   Do not open any other rows

**Continued with other two collapsible headings**

Required to work with any combination of the collapsible headings open. YES on Heading 2 and Heading 4 equals their rows opening, while No Input/Blank Heading 3 and Heading 5 stay collapsed.

Second Problem:

The table at the bottom I want to autofill.

If headings are left blank, they are deemed "Not Assessed" with an "X".

Any headings opened with "YES" are indicated as PASS or FAIL depending on column J inputs. Any one "F" indicated means FAIL (this includes all headings on the sheet). For example, if heading 3 and 4 are selected and each item has a "P" in J, then heading 6 must also have all "P" inputs in the J column in order to recieve a PASS. Heading 2 and 5 are Not Assessed and automatically have an "X" in the cells.

*P.S. if you know a solution for using the IF function and being blank until a value is entered that'd be helpful too. I've used the formula in J16 and am planning to use it for many cells, I just don't know how to build the formula.

Previous Help:

I was given this code previously but I have adjusted my sheet to more closely match the actual excel. I believe the problem with this coding is the 3rd Section of Unhide/Show the corresponding rows. As my rows are not all 3 rows wide. I don't know how to fix the code to work for me.

I have changed the cell inputs in my new document so the cell numbers used in this code are incorrect now, just as a note. I used data validation and conditional formatting in the "mark" and "result" columns - not sure if that was intended from the code but that's what is in the current sheet.

****************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

Dim checkCells As Range

Dim chk As Range

Dim j As Long, k As Long

Set checkCells = Range("J6,J10,J14,J18") ''' The range cells with Yes/No dropdown

If Not Intersect(Target, checkCells) Is Nothing Then

Application.ScreenUpdating = False

'' 1st- Hide all the data rows

Rows("7:9").EntireRow.Hidden = True

Rows("11:13").EntireRow.Hidden = True

Rows("15:17").EntireRow.Hidden = True

Rows("19:21").EntireRow.Hidden = True

''' 2nd Evaluate the value in each cell in the checkCells range

'' And show rows data only if the value is YES

For Each chk In checkCells

If UCase(chk.Value) = "YES" Then '''' 3rd -Unhide/Show the corresponding rows

chk.Offset(1).Resize(3, 1).EntireRow.Hidden = False

Else '''' Hide the corresponding rows and delete the Test results

chk.Offset(1, 0).Value = ""

chk.Offset(3, 0).Value = ""

End If

Next chk

End If

Application.ScreenUpdating = True

End Sub

*******************************************************************

I appreciate the help!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-03T15:09:40+00:00

    Hi,

    Thank you for the help, but there is a problem with the formula.

    If I indicate YES in Column J on the first heading the rest of the headings are not read and therefor not expanding when I indicate YES, only when the previous heading has no input is it expanding properly.

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2023-03-29T21:58:30+00:00

    Hi,

    I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

    I understand that you are looking to add collapsible rows to your Excel worksheet using VBA, and you are also looking to autofill a table at the bottom of the worksheet based on certain criteria. I'll do my best to help you with that.

    For the first problem, you want to have headings 3, 4, 5, and 6 collapsible, and you want them to start collapsed. When there is a "YES" indication in the J column, you want those rows to open. Rows 1-13, 17, 23, 28, 33-end always need to be visible. To achieve this, you can use the Worksheet_Change event in VBA to monitor changes in the worksheet. I see that you have already been provided with some code, but it needs to be adjusted to work with your current worksheet.

    To do this, you'll need to adjust the range of cells that the code is referencing in the 3rd section where it unhides/shows the corresponding rows. Instead of using fixed row numbers, you can use the Range.Offset property to reference the rows relative to the current cell. For example, instead of using Rows("7:9"). EntireRow.Hidden = True, you can use Target.Offset(1). Resize(3, 1). EntireRow.Hidden = True. This will hide the three rows immediately below the cell that was changed.

    For the second problem, you want to autofill a table at the bottom of the worksheet based on certain criteria. If headings are left blank, they should be marked as "Not Assessed" with an "X". If headings are opened with a "YES", they should be marked as PASS or FAIL depending on the inputs in column J. For example, if heading 3 and 4 are selected and each item has a "P" in J, then heading 6 must also have all "P" inputs in the J column in order to receive a PASS. Heading 2 and 5 are Not Assessed and automatically have an "X" in the cells.

    To accomplish this, you can use an IF statement in the VBA code to check the values in the J column and determine whether a heading should be marked as PASS or FAIL. You can also use an IF statement to check if a heading is blank and mark it as "Not Assessed" with an "X". For example, you can use the following formula in cell J16: =IF(COUNTIF(J7:J15,"F")>0,"FAIL","PASS"). This formula checks if there are any "F" values in the J column for rows 7-15, and if so, marks the heading as "FAIL".

    You can try this updated code :-

    Private Sub Worksheet_Change(ByVal Target As Range) Dim checkCells As Range Dim chk As Range

    Set checkCells = Range("J13,J17,J23,J28") ''' The range cells with Yes/No dropdown

    If Not Intersect(Target, checkCells) Is Nothing Then Application.ScreenUpdating = False

    '' 1st - Hide all the data rows Rows("14:16"). EntireRow.Hidden = True Rows("18:22"). EntireRow.Hidden = True Rows("24:27"). EntireRow.Hidden = True Rows("29:32"). EntireRow.Hidden = True Rows("34:100"). EntireRow.Hidden = True

    ''' 2nd Evaluate the value in each cell in the checkCells range '' And show rows data only if the value is YES For Each chk In checkCells Select Case chk. Row Case 13 If UCase(chk. Value) = "YES" Then '''' 3rd -Unhide/Show the corresponding rows Rows("14:16"). EntireRow.Hidden = False Exit For End If Case 17 If UCase(chk. Value) = "YES" Then '''' 3rd -Unhide/Show the corresponding rows Rows("18:22"). EntireRow.Hidden = False Exit For End If Case 23 If UCase(chk. Value) = "YES" Then '''' 3rd -Unhide/Show the corresponding rows Rows("24:27"). EntireRow.Hidden = False Exit For End If Case 28 If UCase(chk. Value) = "YES" Then '''' 3rd -Unhide/Show the corresponding rows Rows("29:32"). EntireRow.Hidden = False Exit For End If End Select Next chk

    Application.ScreenUpdating = True End If End Sub

    Here's an example formula you can use in cell K38 for the first section:

    =IF(OR(J15="",J16="",J17=""),"Not Assessed",IF(COUNTIF(J15:J17,"F")>0,"FAIL","PASS"))

    For more Information, please refer to following resource "Collapsing Rows in Excel based on "Yes" or "No"" -> https://answers.microsoft.com/thread/b2b47c67-f410-420e-9db5-03cbe581d4df

    If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

    Best Regards, Sneha

    0 comments