Prepare a document before sending it to others

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

Comments are closed.

Pinterest