Delete all grouped rows and columns in active worksheets in a workbook

[vivekdabodiya]

Hello awesome ppl,

I am trying some VBA codes to delete all the grouped rows and columns from active sheets of the excel workbook. The code is attached, however it is not working fine.

Also can it be done something like i simply run the macro and magic is done. the current code give "OK" prompts after it is done with one active sheet.

Thank you so much for all the help in advance.

-Vivek

"
>>> use code - tags <<<

Sub Remove_Grouped()
' Run for multiple sheets

         Dim WS_Count As Integer
         Dim I As Integer
         Dim x As Long
    Dim LastColumn As Long
    Dim rng As Range
    Dim y As Long
    Dim LastRow As Long
    Dim FirstEmptyRow As Long
    Dim FirstEmptyCol As Integer




         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
For I = 1 To WS_Count

 

'PURPOSE: Delete Only Columns that are Grouped within the ActiveSheet



'Retrieve Range of Cells with Data
  Set rng = ActiveSheet.UsedRange

'Determine Last Column in Data Set
  LastColumn = rng.Columns.Count

'Loop Through Columns and Delete Columns that are Grouped
    For x = 1 To LastColumn
        If rng.Columns(x).OutlineLevel > 1 Then
            rng.Columns(x).EntireColumn.Delete
            x = x - 1
        End If
    Next x
   


'PURPOSE: Delete Only Rows that are Grouped within the ActiveSheet
'SOURCE: www.TheSpreadsheetGuru.com


'Retrieve Range of Cells with Data
  Set rng = ActiveSheet.UsedRange

'Determine Last Row in Data Set
  LastRow = rng.Rows.Count

'Loop Through Rows and Delete Rows that are Grouped
    For y = 1 To LastRow
        If rng.Rows(y).OutlineLevel > 1 Then
            rng.Rows(y).EntireRow.Delete
            y = y - 1
        End If
    Next y



With ActiveSheet.PageSetup
        If .PrintArea = "" Then
            Set rng = ActiveSheet.UsedRange
        Else
            Set rng = ActiveSheet.Range(.PrintArea)
        End If
    End With

    FirstEmptyCol = rng.Cells(rng.Cells.Count).Column + 1
    FirstEmptyRow = rng.Rows.Count + rng.Cells(1).Row

    Range(Cells(1, FirstEmptyCol), Cells(1, 256)).EntireColumn.Delete
    Range(Cells(FirstEmptyRow, 1), Cells(Rows.Count, 1)).EntireRow.Delete


' Repeat for multiple sheets
          MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

"

 

[p45cal]

Try:

Sub blah()
Dim RngToDelete As Range
Set Rng = ActiveSheet.UsedRange
LastColumn = Rng.Columns.Count
For x = 1 To LastColumn
  If Rng.Columns(x).OutlineLevel > 1 Then
    If RngToDelete Is Nothing Then Set RngToDelete = Rng.Columns(x) Else Set RngToDelete = Union(RngToDelete, Rng.Columns(x))
  End If
Next x
If Not RngToDelete Is Nothing Then RngToDelete.EntireColumn.Delete
End Sub

and something along the same lines for rows.

Put that together and process ALL the sheets in the active workbook:

Sub blah2()
Dim RngToDelete As Range
For Each sht In ActiveWorkbook.Worksheets
  Set RngToDelete = Nothing
  For Each colm In sht.UsedRange.Columns
    If colm.OutlineLevel > 1 Then
      If RngToDelete Is Nothing Then Set RngToDelete = colm Else Set RngToDelete = Union(RngToDelete, colm)
    End If
  Next colm
  If Not RngToDelete Is Nothing Then RngToDelete.EntireColumn.Delete

  Set RngToDelete = Nothing
  For Each rw In sht.UsedRange.Rows
    If rw.OutlineLevel > 1 Then
      If RngToDelete Is Nothing Then Set RngToDelete = rw Else Set RngToDelete = Union(RngToDelete, rw)
    End If
  Next rw
  If Not RngToDelete Is Nothing Then RngToDelete.EntireRow.Delete
Next sht
End Sub

 

[vivekdabodiya]

Thanks p45cal this one worked like a charm.

I would appreciate if you can provide one single macro to do following in order:-

For active worksheets:-
1. Value paste all data in the cells.
2. Delete all hidden rows and columns.
3. Delete all grouped rows and columns.
4. Delete data outside print area.

For entire workbook:-
1. Value paste all data in the cells.
2. Delete all hidden rows and columns.
3. Delete all grouped rows and columns.
4. Delete data outside print area.

I'm attaching the separate codes for all which I collated so far but gives me error anytime. Hope these will help to some extent.

Thanks,
Vivek