Clear Contents of unprotected Specific Range in a protected sheet

[auhom]

I want to clear the unprotected contents of a protected sheet within a specific range. for example when i run VBA it will clear the contents ranging from A1002:F1301 and G1002: AZ1301. The mentioned ranges are unprotected. I want only the cell between these range to ne clear but not format or any other change . How can i do this ?

[ADezii]

I am a little confused about you request. From my interpretation you wish to clear all Cells within a specified, unprotected Range except a single Cell within that Range. If this is so, then the following Macro will do the trick, it will clear all Cells within the Range $A$1002:$F$1301 except $C$1016.

Dim rng1 As Excel.Range
Dim rng2 As Excel.Range

Set rng1 = Worksheets("Sheet1").Range("A1002:F1301")

For Each rng2 In rng1
  If rng2.Address <> "$C$1016" Then
    rng2.ClearContents
  End If
Next

[zmbd]

ADezii, I think a typo here, "cell" might have been "cells" given that these are adjacent ranges.

Sub poc()
    ThisWorkbook.Worksheets("Sheet1").Range("A1002:AZ1301").ClearContents
End Sub

will do the trick.

If this isn't the case then auhom needs to clarify the question.

[ADezii]

Thanks zmdb for the clarification. For my own curiosity, what is your opinion on using?

ActiveWorkbook vs. ThisWorkbook

[zmbd]

I personally have two to 15 different workbooks open at a time at work and having ran afoul of executing code and having the wrong workbook effected, I tend to use "ThisWorkBo ok" over "ActiveWorkbook ".

[MikeTheBike]

Hi zmbd

I very rarely use ThisWorkbook as it will refer to the workbook in which the running code is written. I hardly ever read or write info to/from the workbook that is running the code.

From memory I only use ThisWorkbook for checking that the user has not selected this file to open ie = ThisWorkbook.Na me (in addition to checking the selected file is not already open).

[ADezii]

@zmbd and Mike:
Thanks for tour input.