Clear Spreadsheet Field
 

Hi,

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

Many thanks, Glenn

Clear Spreadsheet Field
 

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

"Glenn" wrote:

Hi,

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

Many thanks, Glenn

Clear Spreadsheet Field
 

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
---------------
Jacob Skaria


"Glenn" wrote:

Hi,

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

Many thanks, Glenn

Clear Spreadsheet Field
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Glenn" wrote:

Hi,

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

Many thanks, Glenn

Clear Spreadsheet Field
 

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Glenn" wrote:

Hi,

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

Many thanks, Glenn

Clear Spreadsheet Field
 

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

--
Rick (MVP - Excel)



"Glenn" wrote in message
...
Hi,

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

Many thanks, Glenn