如何自动清除未锁定单元格数据

haroldchan

由于每个月的格式及计算公式相同数据不同，请问如何用控件代码自动清除未锁定单元格数据，在线等，谢谢

pureshadow

sub 删除未保护单元格()
for each a in activesheet.usedrange
if a.locked = false then
a.clearcontents
end if
next
end sub
不过，传说这段代码在数据多时运行起来很慢


Sub 兰色版()
Dim mrg As Range, K As Integer
For Each mrg In ActiveSheet.UsedRange
If mrg.Locked = False Then
K = K + 1
If K = 1 Then mrg.Select
Union(Selection, mrg).Select
End If
Next mrg
End Sub

Sub 398版()
    Dim vUnlocked As Variant
    Dim rngUnlocked As Range
   
    With ActiveSheet
        If .ProtectContents = True Then .Unprotect
        vUnlocked = .UsedRange
        .UsedRange.ClearContents
        .Protect
        On Error Resume Next
        .UsedRange.Value = 1
        .Unprotect
        Set rngUnlocked = .UsedRange.SpecialCells(xlCellTypeConstants)
        .UsedRange = vUnlocked
        rngUnlocked.Select
    End With
End Sub