CODEWITHSUNDEEP
excelvba
Buras
Buras

Reputation: 3099

How to check for no value cells ?

I need to delete all rows that contain no value. If a row contains formula or formatting but no value , it is to be deleted

The following works fine only for rows that have no value and formulas

Sub test()
 [a:a].SpecialCells(xlBlanks).EntireRow.Delete
End Sub

However the above does not delete a row if a A cell contains a formula . How can I improve it ?

Upvotes: 0

Views: 96

Answers (3)

Bernard Saucier
Bernard Saucier

Reputation: 2270

Try this :

Sub CleanupCrew()

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1) 
                      'Replace 1 with either the number of your sheet or "itsName"

    last = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For x = last to 1 Step -1
        If ws.Cells(x, 1) = "" Or ws.Cells(x, 1).Formula <> "" Then
            ws.Rows(x).Delete
        End If
    Next x

End Sub

It will loop through all the rows (starting from the last) and delete all those that have either no value or a formula in column A. Hope this is what you were looking for. If not, let me know, I'll provide some more support!

Upvotes: 1

L42
L42

Reputation: 19727

Perhaps somthing like this:

Sheet1.AutoFilterMode = False
[a:a].AutoFilter 1, "="
[a:a].Resize(Rows.Count - 1).Offset(1, 0).SpecialCells( _
    xlCellTypeVisible).EntireRow.Delete
Sheet1.AutoFilterMode = False

Where Sheet1 is the worksheet codename you're working on.
So basically, this filters all Blanks and deletes it regardless if it's a No value blank or Formula blank (e.g. ="")

Upvotes: 1

Trace
Trace

Reputation: 18859

You can use the method HasFormula on the range object: 

Range.HasFormula

will return true if the range contains a fomula, and false if it doesn't.

Upvotes: 1

Related Questions