VBA to run some functions

[vivekdabodiya]

Hi awesome people!

I am looking for two VBA Macros which can do the following in the order :-

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.

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.

Have been long looking out solution for the same. Please help!

Thanks in advance!!

 

[vivekdabodiya]

Sub WorkbookValuePasteAndDelete()
    Dim ws As Worksheet

    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Sheets
        ws.Cells.Copy
        ws.Cells.PasteSpecial xlPasteValues
        ws.Cells.PasteSpecial xlPasteFormats

        ws.Columns.Hidden = False
        ws.Rows.Hidden = False
        ws.Outline.ShowLevels RowLevels:=0, ColumnLevels:=0

        ws.UsedRange.Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

        ws.PageSetup.PrintArea = ""
        ws.PageSetup.PrintArea = ws.UsedRange.Address
    Next ws
End Sub

Sub ActiveSheetValuePasteAndDelete()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ws.Cells.Copy
    ws.Cells.PasteSpecial xlPasteValues
    ws.Cells.PasteSpecial xlPasteFormats

    ws.Columns.Hidden = False
    ws.Rows.Hidden = False
    ws.Outline.ShowLevels RowLevels:=0, ColumnLevels:=0

    ws.UsedRange.Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    ws.PageSetup.PrintArea = ""
    ws.PageSetup.PrintArea = ws.UsedRange.Address
End Sub

Thanks @ Shili12, but this one gives me an error says cannot use command on overlapping sections.

see attached for the same. and when i hit debug..it points me to the code

"Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete"

Can you help rectify..i have hundreds of sheets where i need to perform these operations.

thanks again..really appreciated..