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

x
How to Unmerge Cells and Fill Down Values in Excel

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

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

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
insert commetns in protected workshee3

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
convert text to upper cases1

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)
hide every other row1

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 ...

How to Disable the Save As Prompt in Excel
disable save as prompt1

This post will show you how to use a VBA Macro to save an Excel file and overwrite any existing file without a prompt so that you are going to get the little window that says file already exists do ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Cells that Contain negative Numbers in Excel

This post will guide you how to count the number of cells that contain negative numbers within a range of cells using a formula in Excel 2013/2016.You can count the number of negative numbers in your data using easy functions ...

How to Count Cells Are Not Blank or Empty in Excel
count non blank nonempty cells5

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular ...

How to Count Cells Less Than a Specific Value in Excel
count cells lessr than5

This post will guide you how to count the number of cells less than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are less than a specific ...

How to Count Cells Greater Than a Specific Value in Excel
count cells greater than 5

This post will guide you how to count the number of cells greater than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are greater than a specific ...

Sidebar