How can I clear the contents of (only) hidden cells in Excel (and not delete them)?

Question

(context: I know a lot about writing complex formulas in Excel, but have very basic understanding of VBA. I am amazed that I couldn't find a solution to the problem I am trying to solve on my own which makes we wonder if there is something in Excel that makes it impossible to do). I would like to imagine many others would find this as beneficial as I will because I'm sure lots of people get bloated Excel templates.

I have an Excel template with a variety of cells across a variety of worksheets with pre-populated formulas that update based on dumping a changing set of data into one worksheet. I use the template for different 'projects' that do not all require the use of all the cells pre-populated with formulas. So, once new data is imported, certain unused rows and columns are hidden (either manually or with a VBA macro) because they are not needed.

I would like to find a way to clear the contents (i.e. the formulas) in all the hidden cells to reduce the overall file size. My files quickly get over 100 MB, but perhaps 80% of the cells that contain formulas are not used for any particular project and could be cleared.

I'm not a VBA expert, but I've searched for this through Google, thinking it would be an easy snippet of VBA to find, but after literally hours of going deep into Google search result ranks, I haven't found a single good result that sent me in the direction of a solution.

There is a lot of content about how to use VBA to delete hidden rows or columns, but I can't do that with my workbook (I need the the rows and columns to remain for other formulas and formats to work). I just want to remove the unused formulas in hidden cells.

I understand that I can use the following to clear contents and/or clear formats

Sheets("Test").Range("A1:C3").ClearContents
Sheets("Test").Range("A1:C3").ClearFormats

and I use this line to hide cells:

Range.Hidden = True

So, how can I get the range for the ClearContents function be be defined as hidden cells (or to have it only act on hidden cells)? And, how can this be done efficiently...I've learned that running a loop can take a great deal of time as it progressing through rows or columns to perform a task.

I would like to have a solution that allows for this process:

1. Import new data
2. Hide unneeded rows and columns
3. Push a macro button to clear all formulas from all cells within a certain Range(A3:TY53) across all worksheets

Any help or pointing me to an existing solution I wasn't able to find on my own would be greatly appreciated.

Answer 1

To clear the contents of hidden cells:

Sub KlearHidden()
    Dim r As Range

    For Each r In ActiveSheet.UsedRange
        If r.EntireRow.Hidden Or r.EntireColumn.Hidden Then
            r.Clear
        End If
    Next r
End Sub