VBA Remove format from empty cells in Excel

Question

I need a quick code to clean the format of all the cells that are empty. I have written this code, but it is too slow. Is there a way to make it quicker?

Sub removeFormatEmpty()
    'Declaration of variables
    Dim sheet As Worksheet
    Dim rcell As Range

    For Each sheet In Worksheets
        sheet.Activate
        'Cells.UnMerge
        For Each rcell In sheet.UsedRange.Cells
            If rcell.MergeCells = True Then
                rcell.UnMerge
            End If
            If rcell.Value = "" Then
                rcell.ClearFormats
            End If
        Next rcell
    Next sheet
End Sub

This code works, however it is slow as it needs to go cell by cell. Is there a way to select the whole range except the cells with content?

Update: Thank you to the comments of bobajob and jordan I've been able to update the code and make it much more faster and optimized. It is the new code:

Sub removeFormatEmptyImproved()
    Dim sheet As Worksheet
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    For Each sheet In Worksheets
        'sheet.Activate
        sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
    Next sheet

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

So now it is solved.

You don't need to `Activate` the sheet to make changes in it, also use `Application.ScreenUpdating = False` and `Application.Calculation = xlCalculationManual` at the start of the sub to speed things up, but remember to set them back to `True` and `xlCalculationAutomatic`, respectively, at the end of your sub — Jordan, Dec 22 '16 at 15:52

score 2 · Accepted Answer · edited May 23 '17 at 12:08

Firstly, you don't have to check whether a cell is merged before unmerging it. So to unmerge all cells in sheet...
```
sheet.UsedRange.UnMerge
```
You don't need to activate a sheet before altering it
As mentioned in the comments, you can alter all cells at once by using
```
sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
```

Turning Calculation to manual and ScreenUpdating to false is an easy go-to method to speed most VBA code up!

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' <other code>
' Include error handling so that these are always set back!        

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

So your resulting Sub would be

Sub removeFormatEmpty()

    Dim sheet As Worksheet

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    For Each sheet In ThisWorkbook.Worksheets   

        sheet.UsedRange.UnMerge

        sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats

    Next sheet

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

A final step to speed things up would be to dig into your UsedRange a little more. It can be notorious for remembering long-unused cells and being far bigger than necessary. If you know your sheet layout, there may be a way to restrict the range you are working with.

See some methods for doing this here: Getting the actual usedrange

VBA Remove format from empty cells in Excel

1 Answers1

Linked