Need help speeding up Clear cells and merged cell

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

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Artik