Macro Button that clears constants AND a specific formula

Question

I need a macro for a button that will encompass E2:E180 and only delete constants (or input values) AND the formula "=Left(b..."

I currently use a macro that, on single click, inputs "=LEFT(B" & R & ", 3)" into the active cell; however, I also have other formulae in this column that I want to keep there, like:

=IF(AND(COUNTIF(E267,"TRUE"),COUNTIF(E21,"M*")),"SS","")
=e9 ... etc.
=IFNA(E273,"")
=IF(E39>0,E39,"")

and some others.

I originally tried making a simple macro that cleared the contents of specific cells, but the range selection was too long and would involved editing too many numbers if I was to ever insert a row.

I eventually settled on:

    With Worksheets("MISC.")
        .Range("H4:H182").Copy
    End With
        Range("E2").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll UP:=150
    Range("E3").Select

this basically copies an identical column from the back of the workbook and pastes it over top of the active one. This works, but I would like to know if there is a cleaner way to clear the cells around the important formula without convoluting the code.

In summation, I need a macro that will only target constants and =LEFT(b2,b3,b4,b5... etc.

Answer 1

Please, try the next way, not needing any iteration...

Sub procConstFormulae()
 Dim ws As Worksheet, rng As Range
 
 Set ws = ActiveSheet
 Set rng = ws.Range("E2:E180")
 With rng
    On Error Resume Next
     .SpecialCells(xlCellTypeConstants).Formula = "=""""" 'place nullstring formula instead of constants
     .SpecialCells(xlCellTypeBlanks).Formula = "="""""    'place nullstring formula instead of empty strings
    On Error GoTo 0
    'keep something else than formulae starting with "=Left(B"
    .Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.address & "),7)=""=Left(B"","""",FORMULATEXT(" & rng.address & "))")
    .Replace "=""""", "" 'replace the previous empty string formulae, placed to not bother the above formula...
 End With
End Sub

Edited:

I found an even more compact way. Please, test the next version:

Sub procConstFormulae_()
 Dim ws As Worksheet, rng As Range
 Set ws = ActiveSheet: Set rng = ws.Range("E2:E180")
 
 With rng
    'keep something else than formulae starting with "=Left(B" and return #N/A (string) for no formulae or blank cells:
    .Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.address & "),7)=""=Left(B"","""",FORMULATEXT(" & rng.address & "))")
    On Error Resume Next 'for the case of no blank or constant cells:
     .SpecialCells(xlCellTypeConstants).ClearContents 'the previous line returns errors AS STRING (#N/A) for no formulae cells
    On Error GoTo 0
 End With
End Sub