Programmatically group and ungroup rows based on the active cell

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
I have a worksheet that is based on properties (Real Estate) and all the rows for that property are grouped and hidden. I already have the macro that I run when the rows or columns are ungrouped but I would like to ungroup them programmatically.

If the active cell is on column A (column with the street address) I want it to unhide the rows starting with the row below the active cell.

Current example: Rows 709 through 903 are all grouped and hidden. Cell 708 has the property address. I want to select cell 708 and run the macro, if the cells are grouped/hidden the macro will ungroup/unhide the rows associated with the property. If the cells are already ungrouped/unhidden it should do nothing.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

This is a bit clunky and cumbersome (maybe someone can improve on it), but try

VBA Code:
Sub UNGROUP_UNHIDE()
    Selection.Offset(1, 0).Select
    If Rows(ActiveCell.Row).Hidden = False Then Exit Sub
    MY_START = ActiveCell.Row
    Do Until Rows(ActiveCell.Row).EntireRow.Hidden = False
        Rows(ActiveCell.Row).Hidden = False
        Selection.Offset(1, 0).Select
    Loop
    MY_END = ActiveCell.Row
    Rows(MY_START & ":" & MY_END - 1).Rows.Ungroup
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top