Need help speeding up Clear cells and merged cell

[SHELTONUNDERDOG]

Hello, I am two codes that I am calling when I click an icon in office 365's Excel. The two VBA's that I have, I found online looking for a code to do this process. I am not a VBA writer so that is why I am stuck in this spot right now. I ran the code and it took a little over two minutes to run. I know it could be faster if the two items did not look at every cell on the active sheet, but only the area that I allow people to input info. N3:AZ500 is my range that I need these two to look at instead of the whole sheet. Can anyone help out on changing the Range? I understand what the code is doing, it's just the Dim Rng As Range and such is where I get lost. Thanks again.

'THIS WILL CLEAR OUT ANY MERGED CELLS WHERE THE PROTECTION OPTION IN FORMAT CELL IS NOT SELECTED TO LOCKED
Sub ClearMergedCells()
    Dim Rng As Range, cel As Range, rClearA As Range
    Set Rng = ActiveSheet.UsedRange
    For Each cel In Rng
        If cel.MergeCells And cel.Locked = False Then
            If rClearA Is Nothing Then
                Set rClearA = cel
            Else
                Set rClearA = Union(rClearA, cel)
            End If
        End If
    Next cel
    rClearA.ClearContents
End Sub

'THIS WILL CLEAR OUT ANY UNMERGED CELLS WHERE THE PROTECTION OPTION IN FORMAT CELL IS NOT SELECTED TO LOCKED
Sub ClearUnlockedCells()

    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell

End Sub

'THIS CALLS THE ABOVE TWO CODES AND RUNS THEM
Sub CLEAN_SHEET()
Call ClearMergedCells
Call ClearUnlockedCells
End Sub

[6StringJazzer]

To answer your direct question you would make this change:

    Set Rng = ActiveSheet.Range("N3:AZ500")

However, this may not give much speed improvement. That's still a lot of cells (almost 20,000), and I can't think of a way to do this without checking them individually.

Also merged cells will almost always cause problems and should be avoided.

[SHELTONUNDERDOG]

Thanks, it worked and was faster but not fast. Your right about the amount of cells still slowing it down.

[Artik]

UsedRange limits the search to a certain range (used range), so the whole sheet is not searched. However, if the used range is significantly larger than the range N3: AZ500, then in fact quite a lot of unnecessary cells are processed. To limit the scope of searches, we can use the SpecialCells property. I don't know what type of data to expect in unlocked cells. If they are only constant values, you can search the range:

 ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

when there are only formulas, replace the constant with xlCellTypeFormulas. If both types are present, this becomes a problem, create two loops for each type separately. Also note that when using SpecialCells, the sheet cannot be protected at this point.

The ClearUnlockedCells macro has a performance problem, because it cleans the cells one at a time. To speed up its operation, it should be built similarly to ClearMergedCells - create a union.

Assuming that the unprotected cells can contain only constants (without formulas), the sheet is not protected at this point (the photo and the assumption of protection can be added to the procedure) and that we always execute the main macro CLEAN_SHEET, i.e. we want to clean the merged and non-merged cells, the procedure could look like this:

Sub CLEAN_SHEET_1()
    Dim Rng As Range, cel As Range, rClearA As Range
    Dim i           As Long
    
    With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
    End With

    'Set Rng = Range("N3:AZ500").SpecialCells(xlCellTypeConstants)
    'Or
    Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

    For Each cel In Rng
        If Not cel.Locked Then
            i = i + 1

            If rClearA Is Nothing Then
                Set rClearA = cel
            Else
                Set rClearA = Union(rClearA, cel)
            End If

            'When a large union of ranges is formed, it is better to clean the range in batches.
            If i >= 500 Then
                rClearA.Value = Empty
                i = 0
                Set rClearA = Nothing
            End If

        End If
    Next cel

    If Not rClearA Is Nothing Then
        rClearA.Value = Empty
    End If
    
    With Application
      .Calculation = xlCalculationAutomatic
      .ScreenUpdating = True
    End With

    MsgBox "Done.", vbInformation
End Sub

Artik