How to Allow Group Ungroup Rows on Protected Sheet in Excel

,

This post will guide you how to allow “Group/Ungroup” rows on a protected Worksheets in your Excel. How to group or ungroup rows in a worksheet in Excel 2013/2016.

Group/Ungroup Rows


Assuming that you have lots of rows or columns and you want to group rows or columns or automatically created an outline to make your data easy to organize and declutter spreadsheets. You can use the below steps to group rows:

Step1: select a set of rows that you want to group together. Such as: Row1 to Row3.

group ungroup rows in protected sheet1

Step2: go to Data tab in the Ribbon, and click on the Group command under the Outline Group. And then select on Group… from the drop-down menu list.

group ungroup rows in protected sheet2

Step3: you should see that a set of dots next to each row would be created and a small box with a minus sign will be created and it can be used to expand the group.

group ungroup rows in protected sheet3

group ungroup rows in protected sheet4

If you want to ungroup rows and you just need to select the groups that you want to ungroup, and then click on the Data tab, and then click on ungroup button located in the Outline Group, and click Ungroup…. From the drop-down menu list.

group ungroup rows in protected sheet5

Allow Group on Protected Sheet


If you have grouped rows in your unprotected worksheet, and you can click on the minus sign(-) or plus sign (+) to collapse the group. And you are not allowed to click minus sign to collapse the group.

group ungroup rows in protected sheet6

If there is a way to group rows in a protected worksheet. The below steps will show you how to use an VBA Macro to protect worksheet that allow you to group rows. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

group ungroup rows in protected sheet7

Sub allowGroup()
    Dim mySheet As Worksheet
    Set mySheet = Application.ActiveSheet
    Dim myPW As String
    myPW = Application.InputBox("Type one Password to protect your worksheet:", "allowGroup", "", Type:=2)
    mySheet.Protect Password:=myPW, Userinterfaceonly:=True
    mySheet.EnableOutlining = True
End Sub

Step5: back to the current worksheet, click on Macros button under Code group. then click Run button.

group ungroup rows in protected sheet8

Step6: you need to type one password to protect your current worksheet, click on Ok button.

group ungroup rows in protected sheet9

Step7:  Now you can click on the minus sign(-) or plus sign (+) to collapse the group on your protected worksheet.

group ungroup rows in protected sheet10