Deselect Subrange

Question

I want to lock the range A4:B9 in my sheet. Now as it looks like the only way to accomplish that is to set the Lock=false status to all cells I do not want to be locked and then protect the whole file.

My problem is, that I have just a little range that has to be protected, so I need to find a way to create a range with all cells but e.g. A4:B9. I know intersect and union but cannot come up with an idea to apply them to get my goal accomplished.

score 0 · Accepted Answer · answered May 05 '15 at 15:56

I wrote this that works for me:

Sub deselect_subranges()
Dim cell As Range
Dim rngAll As Range
Dim rngMy As Range
Dim rngNew As Range

Set rngAll = ThisWorkbook.Worksheets(1).Range("A1:AZ400")
Set rngMy = ThisWorkbook.Worksheets(1).Range("C3:E8")

Set rngNew = Nothing

For Each cell In rngAll
    If Intersect(cell, rngMy) Is Nothing Then
        If rngNew Is Nothing Then
            Set rngNew = cell
        Else
            Set rngNew = Union(rngNew, cell)
        End If
    End If
Next cell

rngNew.Select

End Sub

Deselect Subrange

1 Answers1