deleting cells with formula

[HanSam]

output.Range("BF2").AutoFill destination:=output.Range("BF2:BF" & Cells(Rows.Count, "H").End(xlUp).Row)

The code above looks up to Column H and autofills Column BF with formula up to Rows of Column H that has value. This code is triggered by another code which fills up Column H. If for example Column H had 1000 rows, Column BF will have 1000 rows as well with formulas.

If, Column H had lesser rows on the next fill up - the 1000 rows of BF will still have the formulas. Is there any way to remove these formulas through VBA?

 

[shrivallabha]

You can delete all cells in column BF which are not required.

output.Range("BF2").AutoFill Destination:=output.Range("BF2:BF" & Cells(Rows.Count, "H").End(xlUp).Row)
Dim lnglastrow As Long
lnglastrow = Range("BF:BF").Find("*", , xlFormulas, , , xlPrevious).Row
If lnglastrow > Cells(Rows.Count, "H").End(xlUp).Row Then
    output.Range("BF" & Cells(Rows.Count, "H").End(xlUp).Row + 1 & ":O" & lnglastrow).Delete xlUp
End If

 

[Monty]

Hello HanSam.

Another way!

Sub RemoveFormula()

    Dim rConstants As Range

    Set rConstants = Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)
    rConstants.ClearContents

End Sub

 

[shrivallabha]

Hi Monty,

The code you've posted will delete all cells in the sheet having formula which is not the intention of OP.

 

[HanSam]

Thanks @shrivallabha and @Monty for the responses!!

@shrivallabha suggestion was perfect! Thanks!