How to bypass cell protect message/cursor focus set

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

To clear everything to the right:
Range(Selection, Selection.End(xlToRight)).ClearContents

To clear the entire row:
Selection.EntireRow.ClearContents

But, surrounding the table are cells protected. When I use both of
abovement ClearContents solutions, I get a message that protected cells
cannot be modified. How to bypass this (so no modification, but also no
message).

A solution would be to have the focus automatically moved to the most
left cell of the active row, and then use Entire Row or the fixed
amount of cells to the right to be cleared.

Bart
 
Hi Bart,

Try something like:

'=============>>
Public Sub Tester()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range(Selection, Selection.End(xlToRight))
Set rng1 = Intersect(rng1, Range("MyTable"))

If Not rng1 Is Nothing Then
rng1.ClearContents
End If

Set rng2 = Intersect(Selection.EntireRow, Range("MyTable"))

If Not rng2 Is Nothing Then
rng2.ClearContents
End If

End Sub
'<<=============
 
Hi Norman, your solution is great!
I also made a Clear Column-button, using xlDown and EntireColumn.

Bart
 
Back
Top