Clear Spreadsheet Field

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

Hi,

I would like a bit of VB code that when clicked will clear all unprotected
fields within a workbook.

Many thanks, Glenn
 
Sub demo()

Dim r As Range
Dim rng As Range
Dim c As Range

Dim ws As Worksheet

Set ws = ActiveSheet
Set r = ws.Cells.SpecialCells(xlCellTypeLastCell)
Set rng = Range(ws.Range("A1"), r)

For Each c In rng.Cells
If c.Locked = False Then c.ClearContents
Next c

End Sub
 
Try

Sub Macro2()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If Not cell.Locked Then cell.ClearContents
Next
End Sub

If this post helps click Yes
 
You said Workbook ?

Sub Macro2()
Dim cell As Range, ws As Worksheet
For Each ws In Worksheets
For Each cell In ws.UsedRange
If Not cell.Locked Then cell.ClearContents
Next
Next
End Sub

If this post helps click Yes
 
Or

Sub Macro2()
Dim cell As Range
dim ws as worksheet
for each ws in activeworkbook.worksheets
For Each cell In ws.UsedRange
If Not cell.Locked Then cell.ClearContents
Next cell
next ws

End Sub
 
You could give this routine a try; it will clear all unlocked cells on all
worksheets in the active workbook (and it should be pretty fast)...

Sub ClearUnlockedCells()
Dim C As Range, FoundCells As Range, SheetName As String
Dim WS As Worksheet, FirstAddress As String
Application.ScreenUpdating = False
Application.FindFormat.Locked = False
SheetName = ActiveSheet.Name
For Each WS In Worksheets
WS.Activate
Set FoundCells = Nothing
With WS.UsedRange
Set C = .Find("", SearchFormat:=True)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If FoundCells Is Nothing Then
Set FoundCells = C
Else
Set FoundCells = Union(FoundCells, C)
End If
Set C = .Find("", after:=C, SearchFormat:=True)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
If Not FoundCells Is Nothing Then FoundCells.Clear
End With
Next
Application.FindFormat.Clear
Worksheets(SheetName).Activate
Application.ScreenUpdating = True
End Sub
 
Back
Top