Intersect - combining statements to speed processing

[joey peanuts]

Is there any way to combine the (2) intersect statements I am using for each named range? Even though there are only 16 of them, the code slows way down. I was using offset before, but it got confusing keeping track of the numbers. By using the named ranges, the code is easier to read. Plus, if I add or delete columns, I don't have to redo my offset values.

This is put into Private Sub Worksheet_Change(ByVal Target As Excel.Range).

Set vRange1 = Range("RBARECT_STYLE")
If Union(Target, vRange1).Address = vRange1.Address Then

If target = "" Then

Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_HINGE")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_HINGE")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_RATIO")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_RATIO")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_WIDTH")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_WIDTH")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_HEIGHT")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_HEIGHT")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_OPER")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_OPER")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_TEMP")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_TEMP")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_OBSPATT")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_OBSPATT")) = ""
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_SCREEN")).Locked = True
Intersect(Range(Cells(Target.Row, 1), Cells(Target.Row, 100)), Range("RBARECT_SCREEN")) = ""
​

End if
​

End if

Thank you!

 

[shg]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Union(Target, Range("RBARECT_STYLE")).Address = Range("RBARECT_STYLE").Address Then
        If IsEmpty(Target.Value) Then
            With Intersect(Cells(Target.Row, 1).Resize(, 100), _
                           Range("RBARECT_HINGE, RBARECT_RATIO, RBARECT_WIDTH, " & _
                                 "RBARECT_HEIGHT, RBARECT_OPER, RBARECT_TEMP, " & _
                                 "RBARECT_OBSPATT, RBARECT_SCREEN"))
                .Locked = True
                .ClearContents
            End With
        End If
    End If
End Sub

 

[joey peanuts]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Union(Target, Range("RBARECT_STYLE")).Address = Range("RBARECT_STYLE").Address Then
        If IsEmpty(Target.Value) Then
            With Intersect(Cells(Target.Row, 1).Resize(, 100), _
                           Range("RBARECT_HINGE, RBARECT_RATIO, RBARECT_WIDTH, " & _
                                 "RBARECT_HEIGHT, RBARECT_OPER, RBARECT_TEMP, " & _
                                 "RBARECT_OBSPATT, RBARECT_SCREEN"))
                .Locked = True
                .ClearContents
            End With
        End If
    End If
End Sub

Thank you - I'll give that a try.