CODEWITHSUNDEEP
excelvbamacrosformula
markerbean
markerbean

Reputation: 145

VBA/Macro to delete entire row with "only formula"

How can i delete all rows that only contain formula in Column A? I need to delete it so that i won't have problem in selecting range or when recording macro.

I tried:

 Selection.AutoFilter
    ActiveSheet.Range("$A:$L").AutoFilter Field:=1, Criteria1:="#REF!"
    Rows("720:720").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-27
    ActiveSheet.Range("$A$1:$L$719").AutoFilter Field:=1

and:

    Dim c As Range
    Dim SrchRng

    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A1000000").End(xlUp))
    Do
        Set c = SrchRng.Find("=+LEFT(#REF!,2)", LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.delete
    Loop While Not c Is Nothing

but nothing works. I tried to delete all rows that has #REF or the formula itself "=+LEFT(#REF!,2)" but still no luck.

I need to delete all/entire rows that has only formula. These formulas are in Column A

Upvotes: 0

Views: 772

Answers (2)

user3598756
user3598756

Reputation: 29421

try this

Sub DeleteRowsWithFormulas()
ActiveSheet.Columns("A").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
End Sub

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

please see the below code

Sub DeleteFormulaCells()
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lastrow
        If Range("A" & i).HasFormula() = True Then
            Rows(i).EntireRow.Delete
            lastrow = lastrow - 1
        End If
    Next i
End Sub

Upvotes: 1

Related Questions