VBA to clear columns but not protected cells

[oblix]

Hi there want to create vba to clear cells on spread sheet
In columns G, and I to AM
The problem is that there are protected cells I dont want to clear
I tried something like this in column h to test but it also clears the protected cells

Sub EmptyStockCount()

On Error Resume Next
Sheets("Blank Stock Sheet").Columns("h:h").Value = vbNullString
On Error GoTo 0

End Sub
Dont understand what I amdoing Wrong
please some assist

 

[Marc L]

Hi, a VBA demonstration as a beginner starter :​

Sub Demo1()
         Dim Rg As Range
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    With Sheets("Blank Stock Sheet")
        For Each Rg In .Range(Replace("G1:G#,I1:AM#", "#", .UsedRange.Rows(.UsedRange.Rows.Count).Row))
          If Not Rg.Locked Then Rg.ClearContents
        Next
    End With
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[oblix]

Thank you for reply
I tried this but I forgot to mention that column g has merged cells
Therefore the code gives error
merged like g9&g10, g11&g12 and so it goes on
Is there a solution?

 

[Alex Blakenburg]

As far as I can tell if you make this change it should work.
(Added ".MergeArea")

Change this:

If Not Rg.Locked Then Rg.ClearContents

To this:

If Not Rg.Locked Then Rg.MergeArea.ClearContents

 

[oblix]

Thank you to both Marc L and Alex Blandenburg. Between the two of you my problem is solved.
Solution working fine

 

[Alex Blakenburg]

Thanks for letting us know. Glad we were able to help and that @Marc L’s solution is now working for you.

 

[oblix]

Does clearing 650 lines take long....seems to take long to clear cells.

 

[Rick Rothstein]

See if my non-looping method of doing what you asked for is any faster...

Sub Test()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Range("G:G", "I:AM").Replace "", "", SearchFormat:=True, ReplaceFormat:=False
  Application.FindFormat.Clear
End Sub

 

[oblix]

Maybe I am missing something Rick..I tried but all cells are still filled.
Also dont see that it specifically only clears those fields in the "Blank Stock Sheet"

 

[Rick Rothstein]

Sorry, I missed that you were not going to run the code from the active sheet. See if this works for you now...

Sub Test()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Sheets("Blank Stock Sheet").Range("G:G", "I:M").Replace "", "", SearchFormat:=True, ReplaceFormat:=False
  Application.FindFormat.Clear
End Sub