Using IsNumeric to delete rows of data

Question

I have two columns of data I am cleaning up using VBA. If the value in column A is non-numeric or blank, I need to delete the entire row. Below is a sample of the data and the code I am trying to use. It seems to be completely skipping over the portion of the code that deletes the rows if IsNumeric returns false.

9669    DONE
9670    OPEN
Order # STATUS

9552    
9672

Code that isn't working.

Dim cell As Range

For Each cell In Range("A1:A" & max_col)
    If IsNumeric(cell) = False Then
        Cells(cell, 1).Select
        Rows(cell).EntireRow.Delete
        Exit For
    End If
Next cell

Any help is appreciated!

DisplayName · Accepted Answer · 2018-03-20T19:46:33.963

use just

    With Range("A1", Cells(Rows.Count, 1).End(xlUp))
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    End With

or, if you don't know for sure whether there will be empty or not numeric cells

    With Range("A1", Cells(Rows.Count, 1).End(xlUp))
        If WorksheetFunction.CountBlank(.Cells) > 0 Then .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        If WorksheetFunction.Count(.Cells) < .Rows.Count Then .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    End With

score 3 · Answer 2 · answered Mar 20 '18 at 19:30

3

Loop from the bottom

Dim max_col as long
max_col = 100
Dim i as Long
For i = max_col to 1 step -1
   If Not isnumeric(activesheet.cells(i,1)) then
       activesheet.rows(i).delete
   End If
Next i

answered Mar 20 '18 at 19:30

Scott Craner

148,073
10
49
81

score 3 · Answer 3 · answered Mar 20 '18 at 19:32

When deleting (or adding, for that matter) rows you need to loop backwards through the data set - See @ScottCraner's example for an exact answer like that - or, you create the range of cells to delete then delete at once, like below:

Dim rowNo as Long

For rowNo = 1 to max_col

    Dim cell as Range
    Set cell = Range(rowNo,1) 

    If IsNumeric(cell) Then

        Dim collectRows as Range
        If collectRows is Nothing Then
            Set collectRows = cell
        Else
            Set collectRows = Union(collectRows,cell)
        End If

    End If

Next

collectRows.EntireRow.Delete

Using IsNumeric to delete rows of data

3 Answers3