VBA一次选定所有未锁定单元格

guideming · 发表于 2008-2-19 20:43

Sub tt() 牋 Dim x As Range, y As Range 牋 For Each y In Range("A1:iv65536") 牋牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub

兰色幻想 · 发表于 2008-2-19 20:46

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用guideming在2008-2-19 20:43:00的发言： Sub tt() 牋 Dim x As Range, y As Range 牋 For Each y In Range("A1:iv65536") 牋牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub</div>循环太多了 另：能放在一行就放在一行，不适合放在一行的还是分开放好些。

guideming · 发表于 2008-2-19 20:48

Sub tt() 牋 Dim x As Range, y As Range 牋 For Each y In Range("A1:iv65536") 牋牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub

兰色幻想 · 发表于 2008-2-19 20:52

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用guideming在2008-2-19 20:48:00的发言： Sub tt() 牋 Dim x As Range, y As Range 牋 For Each y In Range("A1:iv65536") 牋牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub </div>还是一样的问题啊

guideming · 发表于 2008-2-19 21:04

Sub tt() ?Dim x As Range, y As Range ?For Each y In activesheet.usedrange 牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub

element · 发表于 2008-2-19 21:06

来个另类的方法，速度相对来说应该是不错的 Sub test()     Dim vUnlocked As Variant     Dim rngUnlocked As Range    With Sheet1         vUnlocked = .UsedRange         .UsedRange.ClearContents         .Protect         .EnableSelection = xlUnlockedCells         On Error Resume Next         .UsedRange.Value = 1         .Unprotect         Set rngUnlocked = .UsedRange.SpecialCells(xlCellTypeConstants)         .UsedRange = vUnlocked         rngUnlocked.Select     End With End Sub

[此贴子已经被作者于2008-2-19 21:05:55编辑过]

兰色幻想 · 发表于 2008-2-19 21:08

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用guideming在2008-2-19 21:04:00的发言： Sub tt() ?Dim x As Range, y As Range ?For Each y In activesheet.usedrange 牋 If y.Locked = False Then If x Is Nothing Then Set x = y Else Set x = Union(x, y) ?Next ?x.Select End Sub</div>你的代码还是排这样更好些, 容易让人看懂. [em05] Sub tt() Dim x As Range, y As Range For Each y In ActiveSheet.UsedRange     If y.Locked = False Then        If x Is Nothing Then           Set x = y         Else            Set x = Union(x, y)         End If      End If  Next  x.Select End Sub

兰色幻想 · 发表于 2008-2-19 21:19

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用element在2008-2-19 21:06:00的发言： 来个另类的方法，速度相对来说应该是不错的 Sub test()     Dim vUnlocked As Variant     Dim rngUnlocked As Range    With Sheet1         vUnlocked = .UsedRange         .UsedRange.ClearContents         .Protect         .EnableSelection = xlUnlockedCells         On Error Resume Next         .UsedRange.Value = 1         .Unprotect         Set rngUnlocked = .UsedRange.SpecialCells(xlCellTypeConstants)         .UsedRange = vUnlocked         rngUnlocked.Select     End With End Sub   </div> 运行不成功, 提示错误 ,试图修改受保护的工作表

element · 发表于 2008-2-19 21:22

To，兰色 看看是否选择了【遇到未处理的错误时中断】这个选项 在【VBE】【选项】【通用】【错误捕捉】下

兰色幻想 · 发表于 2008-2-19 21:24

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用element在2008-2-19 21:22:00的发言： To，兰色 看看是否选择了【遇到未处理的错误时中断】这个选项 在【VBE】【选项】【通用】【错误捕捉】下 </div>有选取

		自动登录	找回密码
密码			注册

VBA一次选定所有未锁定单元格

本帖子中包含更多资源

浏览过的版块