Delete row if first cell isn't numeric vba Excel

Question

I am using following code to delete a row if the first cell is not a number (text or blank cell)

Dim LR3 As Long, i3 As Long
With Sheets("Productos")
LR3 = Range("A" & Rows.Count).End(xlUp).Row
For i3 = LR3 To 2 Step -1
    If IsNumeric(Sheets("Productos").Range("A" & i3).Value) Then
    Else
    Rows(i3).Delete
    End If
Next i3
End With

LR3 to 2 is used because the first row is a header row, and I don't want it deleted. I don't see anything wrong with the code, and I even get no error. Do you see something wrong? It´s maybe a false procedure?

I just rant this in 2010 and it ran as expected. What is happening for you? — Guy Nethery, Feb 19 '14 at 16:18

Siddharth Rout · Accepted Answer · 2014-02-19T16:28:51.980

4

The problem with your code which I suspect is that Sheets("Productos") is not the activesheet. So Rows(i3).Delete is referring to the activesheet which might not be Sheets("Productos")

Notice the use of DOTS in the code below.

Try this (TRIED AND TESTED)

Sub Sample()
    Dim LR3 As Long, i3 As Long

    With Sheets("Productos")
        LR3 = .Range("A" & .Rows.Count).End(xlUp).Row

        For i3 = LR3 To 2 Step -1
            If Not IsNumeric(.Range("A" & i3).Value) Then .Rows(i3).Delete
        Next i3
    End With
End Sub

EDIT: I missed the Blank Cell part but thanks to Jimmy's post, I saw that.

Amended Code

Sub Sample()
    Dim LR3 As Long, i3 As Long

    With Sheets("Productos")
        LR3 = .Range("A" & .Rows.Count).End(xlUp).Row

        For i3 = LR3 To 2 Step -1
            If Not IsNumeric(.Range("A" & i3).Value) Or _
            .Range("A" & i3).Value = "" Then .Rows(i3).Delete
        Next i3
    End With
End Sub

edited Feb 19 '14 at 16:28

answered Feb 19 '14 at 16:18

Siddharth Rout

147,039
17
206
250

2

`adding a sheet selection wouldn't hurt.. – gbianchi 27 secs ago` @gbianchi. You mean `Sheets("Productos").Select`? If yes, then you may want to see [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Feb 19 '14 at 16:21
2

@SiddharthRout has the right idea. When programming in VBA for excel, you never want to use active or select as it will break nearly all the time. Create variables for sheets. Dim ws as Worksheet; Set ws = ThisWorkbook.Sheets("Productos"); – Ryan G Feb 19 '14 at 16:31
@SiddharthRout, that did the job! Thanks – agustin Feb 19 '14 at 16:50
@agustin: Glad to be of help – Siddharth Rout Feb 19 '14 at 16:52

score 3 · Answer 2 · edited Feb 19 '14 at 16:39

3

If the cell is blank, IsNumeric will return true. You could try using len,

Dim LR3 As Long, i3 As Long

With Sheets("Productos")
    LR3 = .Range("A" & .Rows.Count).End(xlUp).Row

    For i3 = LR3 To 2 Step -1
        If IsNumeric(.Range("A" & i3).Value) And _
        len(.Range("A" & i3).Value) > 0 Then

        Else
            .Rows(i3).Delete
        End If
    Next i3
End With

edited Feb 19 '14 at 16:39

Siddharth Rout

147,039
17
206
250

answered Feb 19 '14 at 16:22

Jimmy Smith

2,452
1
16
19

1

+ 1 for not missing the Blank Cell part :) However, `Rows(i3).Delete` is still not fully qualified and will not work if `Sheets("Productos")` is not active :) – Siddharth Rout Feb 19 '14 at 16:29
Thanks, I edited to fully qualify, and the With block is easier on the eyes :) – Jimmy Smith Feb 19 '14 at 16:35
@JimmySmith it's rare. I used your piece of code but it is not deleting blank or non-numeric rows. It's really weird, because Siddharth's did the job :( – agustin Feb 19 '14 at 16:53
It was the qualification that was needed. Odd it is still not deleting blank rows as the len() should return 0. – Jimmy Smith Feb 19 '14 at 16:58

Delete row if first cell isn't numeric vba Excel

2 Answers2