When you send your Excel workbook to other people, sometimes you want to protect or hide certain parts.
For example the formulas you used or temporary data in hidden columns.
You can do this by using Excel's worksheet protection, however you should know that this protection is easily broken and not very secure.
A better method to use might be to create a new version of your workbook in which you replace all the formulas with their calculated results and in which all the hidden data is removed.
The following macro will automatically replace all formulas with their calculated results and removes the hidden columns and rows on the selected worksheets:
Sub RemoveFormulasAndHiddenCells()
' usage: ' 1. select the sheets ' 2. run this macro Dim ws As Worksheet
Dim shSelection As Sheets
Dim i As Long
Dim lMax As Long
Set shSelection = ActiveWindow.SelectedSheets
If MsgBox("Do you want to replace all formulas with their calculated results " _
& " on the selected " & shSelection.Count & " worksheets and then remove " _
& "all hidden rows and columns?" & vbNewLine & "It recommended to save a " _
& "copy of your original workbook first with a different name.", _
vbQuestion + vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
For Each ws In ActiveWindow.SelectedSheets
If ws.Type = xlWorksheet Then
ws.Activate
' change formulas to their calculated results
ws.UsedRange.Copy
ws.UsedRange.PasteSpecial xlPasteValues
Application.CutCopyMode = False
' remove hidden rows:
lMax = ws.UsedRange.Cells(ws.UsedRange.Cells.Count).Row
For i = lMax To 1 Step -1
If Cells(i, 1).EntireRow.Hidden = True Then Rows(i).Delete
Next i
' remove hidden columns:
lMax = ws.UsedRange.Cells(ws.UsedRange.Cells.Count).Column
For i = lMax To 1 Step -1
If Cells(1, i).EntireColumn.Hidden = True Then Columns(i).Delete
Next i
' select the upperleft cell on the worksheet
Cells(1).Select
End If
Next ws
Application.ScreenUpdating = True
shSelection.Select
End Sub