Clear data except Formulas & any coloured items

Topic

New Reply

I have written code to clear data except formulas from all sheet starting from sheet 3 and from A15 to the last row in Col M

I want to amend the formula so that any coloured cells using the fill colour are also not cleared

Code:

 Sub Clear_Data
 Dim Sh As Integer
    Dim LR As Long
    
    For Sh = 3 To Worksheets.Count
        With Worksheets(Sh)
            LR = .Cells(.Rows.Count, "A").End(xlUp).Row
            If LR < 15 Then LR = 15
 On Error Resume Next
            .Range("A15:M" & LR).SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0
                    End With
    Next Sh
End Sub 

It would be appreciated if someone could kindly assist me

Reply | Quote

Replies

Hi Howard

..are you using any conditional formatting that sets cell fill colour?
..or are the cells ‘manually’ coloured??

zeddy

Reply | Quote

Hi Zeddy

These are ‘manually’ coloured

Howard

Reply | Quote

I think you’ll need a loop like this:

Code:

Sub Clear_Data()
    Dim Sh                    As Integer
    Dim LR                    As Long
    Dim cell                  As Range

    Application.ScreenUpdating = False
    For Sh = 3 To Worksheets.Count
        With Worksheets(Sh)
            LR = .Cells(.Rows.Count, "A").End(xlUp).Row
            If LR < 15 Then LR = 15
            On Error Resume Next
            For Each cell In .Range("A15:M" & LR).SpecialCells(xlCellTypeConstants).Cells
                If cell.DisplayFormat.Interior.ColorIndex = xlColorIndexNone Then cell.ClearContents
            Next cell
            On Error GoTo 0
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub

Reply | Quote

Hi Howard

..Rory has a great solution.

For speedup, I would also include turning events and calcs OFF at the start, and turning them back ON at the end.

zeddy

Reply | Quote

Hi Zeddy

Thanks for the tip. It made a huge difference

Reply | Quote

Hi Howard

I find you can also get a macro to run much faster if the VBA editor is closed when you run the macro e.g. assign the macro to a button and run it from there.

zeddy

Reply | Quote

Thanks for the help Rory. Going to turn events and calc off at the start and then back on as it is very slow

Reply | Quote

hi Zeddy

Thanks for the tip

That’s something I did not know, although I do assign macro’s to a button in many instances

Howard

Reply | Quote