How to select all unlocked cells on a sheet?

[Ted M H]

I need a sub procedure that selects all unlocked cells on a worsheet.

Go to special doesn't offer this as an option. What I want to do is akin to
something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead
of blanks I want to select cells where Locked = False.

I've tried a few things with Application.FindFormat.Locked = False, but I
can't figure out to select ALL the FindFormat.Locked = False cells.

Any suggestions?

 

[Barb Reinhardt]

Try using this function to get there.

Option Explicit

Function GetUnlocked(myWS As Excel.Worksheet) As Excel.Range

Dim r As Excel.Range

Set GetUnlocked = Nothing
For Each r In myWS.UsedRange
If Not r.Locked Then
If GetUnlocked = Nothing Then
Set GetUnlocked = r
Else
Set GetUnlocked = Union(GetUnlocked, r)
End If
End If
Next r

End Function

 

[Rick Rothstein]

Give the following macro a try. By default, it works on the ActiveSheet, but
you can change this to a specific worksheet by changing the worksheet
reference in the With statement on the third code line.

Sub ClearUnlockedCells()
Dim C As Range, FoundCells As Range, FirstAddress As String
Application.FindFormat.Locked = False
With ActiveSheet.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.Select
End With
Application.FindFormat.Clear
End Sub

 

[Dave Peterson]

You can either loop through each cell and create a range.

Or you can use the .findformat. It would be nice to use the .findnext method,
but that doesn't work with formatting (at least not in xl2003).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myUnlockedRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

Set wks = ActiveSheet

With wks
'clear any existing formatting that was used
Application.FindFormat.Clear

'just the unlocked cells
Application.FindFormat.Locked = False

Set FoundCell = .Cells.Find(what:="", _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
searchformat:=True)

If FoundCell Is Nothing Then
'not one unlocked cell!
Else
'keep track of where the first one was, so we can quit
'when we find this again.
FirstAddress = FoundCell.Address

'start building the range of unlocked cells
Set myUnlockedRng = FoundCell

Do
Set FoundCell = .Cells.Find(what:="", _
after:=FoundCell, _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
searchformat:=True)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If

'add to the growing range
Set myUnlockedRng = Union(myUnlockedRng, FoundCell)

Loop

End If
End With

If myUnlockedRng Is Nothing Then
MsgBox "None found!"
Else
Application.Goto myUnlockedRng ', scroll:=True
End If
End Sub

 

[Dave Peterson]

I meant to replace this portion of the text:
"Or you can use the .findformat." with the "it would be nice to ..." portion,
but messed up.

 

[Dave Peterson]

Ignore this "correction". I originally wrote what I wanted.

(I reread it as using the .findnext, not .findformat and that won't work.)

(Sigh)

 

[Rick Rothstein]

Your second If..Then test should be this...

If GetUnlocked Is Nothing Then

where I have used the Is keyword in place of your equal sign. I guess you
put your code in the form of a function rather than a macro so that the user
has the flexibility to do more than simply select the unlocked cells. Given
that, the OP should be made aware that he will need to test the return value
from your function for not being Nothing, otherwise the OP's code will error
out if he runs your function against a worksheet that has no unlocked cells
on it.

 

[Gary''s Student]

How about:

Sub GetUnlocked()
Dim r As Range, rUnlocked As Range
Set rUnlocked = Nothing
For Each r In ActiveSheet.UsedRange
If r.Locked = False Then
If rUnlocked Is Nothing Then
Set rUnlocked = r
Else
Set rUnlocked = Union(r, rUnlocked)
End If
End If
Next
If rUnlocked Is Nothing Then
Else
rUnlocked.Select
End If
End Sub


This will select all the unlocked cells in the used ange.

 

[Barb Reinhardt]

Yup, you're right. I didn't test it.

Your second If..Then test should be this...

If GetUnlocked Is Nothing Then

where I have used the Is keyword in place of your equal sign. I guess you
put your code in the form of a function rather than a macro so that the user
has the flexibility to do more than simply select the unlocked cells. Given
that, the OP should be made aware that he will need to test the return value
from your function for not being Nothing, otherwise the OP's code will error
out if he runs your function against a worksheet that has no unlocked cells
on it.

--
Rick (MVP - Excel)




.