Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sort (by one field), copy (based on one field) to another tab,then clear data on original sheet. | Excel Programming | |||
CLEAR FIELD TEXT ON CLICK? | Excel Programming | |||
How to clear content from unprotected Field | Excel Worksheet Functions | |||
How do I clear the Field pulldowns after the data is cleared? | Excel Discussion (Misc queries) | |||
how do i create a button to clear a value field i created? | Excel Programming |