Clear Contents

  • Thread starter Thread starter gibbylinks
  • Start date Start date
G

gibbylinks

I have several sheets in one workbook I use for generating invoices. Is there
a quick way to clear them (they have different setups) leaving the formatting
and formulas intact ?

They are all protected with most cells locked. I am hoping to be able to
have one macro linked to a cell that will work with all the sheets.

thanks

Paul
 
Hi Paul

This macro clear the contents of all unlocked cells in all sheets in the
active workbook.

You can insert a button on a sheet which can call this macro.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell as Range
For Each sh In ThisWorkbook.Sheets
For Each cell In sh.UsedRange
If cell.Locked = False Then cell.ClearContents
Next
Next
End Sub

Regards,
Per
 
Thanks Dave that worked.....but too well.

Two questions.

1) Is there a way to restrict this to the current worksheet only ?
2) Is there a way to define the range cleared ?
(I have hyperlinks and notes on the RHS that I'd like left intact)

Sorry to keep moving the "goalposts"

Paul
 
Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell as Range

set sh = activesheet
For Each cell In sh.Range("a1:B99").cells
If cell.Locked = False Then
cell.value = ""
end if
Next cell

End Sub

Change the address to what you need.
 
Back
Top