Clear Range Without Formulas & Bold Text

[adam]

Hi anyone,

The following code clears data in column G and cell K9 within the specified range. How could the code be modified so that it even limits clearing the formulas in column G?

Code: Select all

Sub ClearReport()
Dim wshS As Worksheet
    Dim wshT As Worksheet
Dim s As Long
    Dim m As Long
Application.ScreenUpdating = False
    Application.EnableEvents = False
    ' Source sheet
    Set wshS = Worksheets("Report")
    ' Get last row
    m = wshS.Range("G:G").Find(What:="THANK YOU").Row
    For s = 15 To m - 1
        If wshS.Range("G" & s).Value <> "Results" Then
            wshS.Range("G" & s).ClearContents
            Worksheets("Report").Range("K9").ClearContents
        End If
    Next s
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Any help on this would be kindly appreciated.

Thanks in advance.

[Rudi]

Hi Adam,

You can try this code...

Code: Select all

Sub ClearReport()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ' Source sheet
    Set wshS = Worksheets("Report")
    ' Get last row
    m = wshS.Range("G:G").Find(What:="THANK YOU").Row
    Worksheets("Report").Range("K9").ClearContents
    For s = 15 To m Step -1
        If wshS.Range("G" & s).Value <> "Results" And _
            Not wshS.Range("G" & s).HasFormula Then
            wshS.Range("G" & s).ClearContents
        End If
    Next s
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[HansV]

Instead of

Mid(wshS.Range("G" & s).Formula, 1, 1) <> "="

you could use

Not Mid(wshS.Range("G" & s).HasFormula

(the original condition could be True if the cell has a text value beginning with an =.)

[Rudi]

TX.
I've updated the code to use your suggestion, edited to: Not wshS.Range("G" & s).HasFormula

[adam]

I'm sorry for the late reply. But the code does not seem to clear the column G even though it does clear the cell K9. The formats of cells in column G are in "Text" format.

Is this the reason why it's not working?

[HansV]

Change the line

Code: Select all

    For s = 15 To m Step -1

in Rudi's macro to

Code: Select all

    For s = 15 To m - 1

as in your original version.

[adam]

Thanks for the help Hans. It works fine now.