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

 

 

Related Posts

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

Sidebar