Macro to remove all formulas and delete hidden sheets, columns and rows from multiple workbooks

[Sorin P]

Hello!

I have to remove all data from multiple files and delete everything that is hidden, so I am looking for a macro that first it will remove all formulas (paste as values) from the files in folders and subfolders, afterwards remove hidden sheets, rows and columns.
I have found an old thread closest to my situation. Can anyone help me with the modifications?

Sub RemoveFormulas()
  
    Dim fName$, wkb As Workbook, wks As Worksheet
    Const folder_path$ = "C:\Temp\Test\"
    Dim fso As Object, fld As Object, fl As Object

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    Call ProcessFolder(fso.GetFolder(folder_path))

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Well done!", vbInformation
  
End Sub

Private Sub ProcessFolder(fld As Object)
    Dim fl As Object, subfld As Object
    For Each fl In fld.Files
        Call RemoveFormulasInternal(fl.Path)
    Next
    If fld.SubFolders.Count > 0 Then
        For Each subfld In fld.SubFolders
            Call ProcessFolder(subfld)
        Next
    End If
End Sub

Private Sub RemoveFormulasInternal(file_path$)
    Dim wkb As Workbook, wks As Worksheet
    Set wkb = Workbooks.Open(file_path, UpdateLinks:=False)
    For Each wks In wkb.Sheets
        wks.Range("A1:AZ100").Copy
        wks.Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next
    wkb.Close SaveChanges:=True
End Sub

Thank you very much!

 

[rollis13]

I'm assuming that in your files to be 'cleaned' there is no data beyond cell AZ100 (otherwise you have to change the range references) or that these files do not contain autorun macros that could get 'upset'.
Add these two lines in your macro:

Private Sub RemoveFormulasInternal(file_path$)
    Dim wkb    As Workbook, wks As Worksheet
    Set wkb = Workbooks.Open(file_path, UpdateLinks:=False)
    For Each wks In wkb.Sheets
        wks.Range("A1:AZ100").Copy
        wks.Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next
    Call DeleteHiddenSheets                       '<- added
    Call DeleteHiddenColumnsRows                  '<- added
    wkb.Close SaveChanges:=True
End Sub

then add these two other macros and have a try on test files (or copies):

Private Sub DeleteHiddenSheets()
    Dim sht    As Worksheet
    'Application.ScreenUpdating = False
    For Each sht In ActiveWorkbook.Worksheets
        Select Case sht.Visible
            Case xlSheetHidden, xlSheetVeryHidden
                Sheets(sht.Name).Visible = xlSheetVisible
                Sheets(sht.Name).Delete
        End Select
    Next sht
    'Application.ScreenUpdating = True
End Sub

Private Sub DeleteHiddenColumnsRows()
    Dim x      As Long
    'Application.ScreenUpdating = False
    For x = Columns.Count To 1 Step -1
        If Columns(x).EntireColumn.Hidden = True Then Columns(x).EntireColumn.Delete
    Next
    For x = Rows.Count To 1 Step -1
        If Rows(x).EntireRow.Hidden = True Then Rows(x).EntireRow.Delete
    Next
    'Application.ScreenUpdating = True
End Sub

 

[rollis13]

Update: a different approch in your macro, move the new Call before the If/Then loop; it would be useless to remove formulas in hidden sheets that are to deleted. Like this:

Private Sub RemoveFormulasInternal(file_path As String)
    Dim wkb    As Workbook, wks As Worksheet
    Set wkb = Workbooks.Open(file_path, UpdateLinks:=False)
    Call DeleteHiddenSheets                       '<- added
    For Each wks In wkb.Sheets
        wks.Range("A1:AZ100").Copy
        wks.Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next
    Call DeleteHiddenColumnsRows                  '<- added
    wkb.Close SaveChanges:=True
End Sub

Ps. maybe not, in the formulas there could be references to cells in the hidden sheets. So, probably the best solution is still the version in post #2.

 

[Sorin P]

Thank you for the approach, I tried to put them all in a single file, but I wasn't able to.
So, starting from the beginning, I have a couple of workbooks that I need to edit. The hidden sheets, rows and columns have formulas that the visible ones are depended.
What I need to do is to first remove all the formulas from all the files (paste as values), then delete all the hidden sheets and afterwards delete all the hidden columns and rows and save all modifications.

I was able to do the first part, remove all the formulas, now I need help to add the macros to delete all the hidden sheets and afterwards delete all the hidden columns and rows and save all modifications.

Sub RemoveFormulas()
  
    Dim fName$, wkb As Workbook, wks As Worksheet
    Const folder_path$ = "d:\!Work\~Test\"
    Dim fso As Object, fld As Object, fl As Object

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    Call ProcessFolder(fso.GetFolder(folder_path))

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Well done!", vbInformation
  
End Sub

Private Sub ProcessFolder(fld As Object)
    Dim fl As Object, subfld As Object
    For Each fl In fld.Files
        Call RemoveFormulasInternal(fl.Path)
    Next
    If fld.SubFolders.Count > 0 Then
        For Each subfld In fld.SubFolders
            Call ProcessFolder(subfld)
        Next
    End If
End Sub

Private Sub RemoveFormulasInternal(file_path$)
    Dim wkb As Workbook, wks As Worksheet
    Set wkb = Workbooks.Open(file_path, UpdateLinks:=False)
    For Each wks In wkb.Sheets
        wks.Cells.Copy
        wks.Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next
    wkb.Close SaveChanges:=True
End Sub

 

[rollis13]

What I need to do is to first remove all the formulas from all the files (paste as values), then delete all the hidden sheets and afterwards delete all the hidden columns and rows and save all modifications.

So that's exactly what my suggestion in post #2 does (forget about post #3, it didn't consider depend formulas). Do exactly what I said in post #2: add the two new lines in your macro "RemoveFormulasInternal" (or substitute the entire macro via Copy/Paste and override). Then Copy/Paste the two new macros into the same vbe module (with your macros) and then launch your macro "RemoveFormulas".

 

[Sorin P]

Thank you very much for explaining and for the patience! I did exactly what you said and it worked almost perfectly!
The only thing is that the rows and columns are deleted only from the active sheet and not the entire workbook. Is it a way to modify the code so that it will work for the entire workbook?

 

[rollis13]

Yes, you are right. Sorry, forgot the code to cycle the sheets but you could have added it by yourself, it was the same as in the other my macro.

Private Sub DeleteHiddenColumnsRows()
    Dim x      As Long
    Dim sht    As Worksheet                       '<- added
    For Each sht In Worksheets                    '<- added
        For x = Columns.Count To 1 Step -1
            If Columns(x).EntireColumn.Hidden = True Then Columns(x).EntireColumn.Delete
        Next
        For x = Rows.Count To 1 Step -1
            If Rows(x).EntireRow.Hidden = True Then Rows(x).EntireRow.Delete
        Next
    Next sht                                      '<- added
End Sub

By the way, to avoid cycling twice through the sheets my two macros could be merged and called from your macro "RemoveFormulasInternal" instead of using the other two. Here is the merged versione:

Private Sub DeleteHiddenSheetsColumnsRows()
    Dim x      As Long
    Dim sht    As Worksheet
    For Each sht In Worksheets
        Select Case sht.Visible
            'delete hidden sheets
            Case xlSheetHidden, xlSheetVeryHidden
                Sheets(sht.Name).Visible = xlSheetVisible
                Sheets(sht.Name).Delete
            Case Else
                'delete hidden columns
                For x = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
                    If Columns(x).EntireColumn.Hidden = True Then Columns(x).EntireColumn.Delete
                Next
                'delete hidden rows
                For x = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
                    If Rows(x).EntireRow.Hidden = True Then Rows(x).EntireRow.Delete
                Next
        End Select
    Next sht
End Sub

 

[rollis13]

Sorry, got it wrong once again. Today isn't my day, forget post #7.
This time I forgot to correctly reference the sheet's name, here is the macro and the merged version updated (done some testing this time):

Private Sub DeleteHiddenColumnsRows()
    Dim x      As Long
    Dim sht    As Worksheet
    'Application.ScreenUpdating = False
    For Each sht In Worksheets
        With Sheets(sht.Name)
            For x = .Columns.Count To 1 Step -1
                If .Columns(x).EntireColumn.Hidden = True Then .Columns(x).EntireColumn.Delete
            Next
            For x = Rows.Count To 1 Step -1
                If .Rows(x).EntireRow.Hidden = True Then .Rows(x).EntireRow.Delete
            Next
        End With
    Next sht
    'Application.ScreenUpdating = True
End Sub

Private Sub DeleteHiddenSheetsColumnsRows()
    Dim x      As Long
    Dim sht    As Worksheet
    For Each sht In Worksheets
        Select Case sht.Visible
            'delete hidden sheets
            Case xlSheetHidden, xlSheetVeryHidden
                Sheets(sht.Name).Visible = xlSheetVisible
                Sheets(sht.Name).Delete
            Case Else
                With Sheets(sht.Name)
                    'delete hidden columns
                    For x = .UsedRange.Columns.Count To 1 Step -1
                        If .Columns(x).EntireColumn.Hidden = True Then .Columns(x).EntireColumn.Delete
                    Next
                    'delete hidden rows
                    For x = .UsedRange.Rows.Count To 1 Step -1
                        If .Rows(x).EntireRow.Hidden = True Then .Rows(x).EntireRow.Delete
                    Next
                End With
        End Select
    Next sht
End Sub

 

[Sorin P]

Thank you very much!!! It worked perfectly!

I will post here the hole code in case someone else needs it

Sub RemoveFormulas()
  
    Dim fName$, wkb As Workbook, wks As Worksheet
    Const folder_path$ = "d:\~Test\"
    Dim fso As Object, fld As Object, fl As Object

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    Call ProcessFolder(fso.GetFolder(folder_path))

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Well done!", vbInformation
  
End Sub

Private Sub ProcessFolder(fld As Object)
    Dim fl As Object, subfld As Object
    For Each fl In fld.Files
        Call RemoveFormulasInternal(fl.Path)
    Next
    If fld.SubFolders.Count > 0 Then
        For Each subfld In fld.SubFolders
            Call ProcessFolder(subfld)
        Next
    End If
End Sub

Private Sub RemoveFormulasInternal(file_path$)
    Dim wkb As Workbook, wks As Worksheet
    Set wkb = Workbooks.Open(file_path, UpdateLinks:=False)
    For Each wks In wkb.Sheets
        wks.Cells.Copy
        wks.Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next
    Call DeleteHiddenSheetsColumnsRows
    wkb.Close SaveChanges:=True
End Sub

Private Sub DeleteHiddenSheetsColumnsRows()
    Dim x      As Long
    Dim sht    As Worksheet
    For Each sht In Worksheets
        Select Case sht.Visible
            'delete hidden sheets
            Case xlSheetHidden, xlSheetVeryHidden
                Sheets(sht.Name).Visible = xlSheetVisible
                Sheets(sht.Name).Delete
            Case Else
                With Sheets(sht.Name)
                    'delete hidden columns
                    For x = .UsedRange.Columns.Count To 1 Step -1
                        If .Columns(x).EntireColumn.Hidden = True Then .Columns(x).EntireColumn.Delete
                    Next
                    'delete hidden rows
                    For x = .UsedRange.Rows.Count To 1 Step -1
                        If .Rows(x).EntireRow.Hidden = True Then .Rows(x).EntireRow.Delete
                    Next
                End With
        End Select
    Next sht
End Sub