Excel --Use VBA to delete all cells with numbers in column?

[Eager VBA Learner]

I am trying to write an Excel VBA macro that will find all cells in Column A that have numbers (as opposed to text) and delete the entire rows (with the numbers in Column A). As a start, I tried writing a routine that I thought should at least enable me to select the first number in the column -- if I selected Cell A1 before running the macro:

Public Sub FindNumber ()
Dim X As Integer
X = 0
Do Until ActiveCell > 0
ActiveCell.Offs et(X, 0).Select
X = X + 1
Loop
End Sub

Unfortunately, even this first step didn't work although all numbers were positive.

If anyone can help this eager but slow VBA learner get back on the right track, I would certainly be most appreciative.

[Killer42]

Just a quick question about your code - when you use .Select, doesn't that change the active cell? (I'm not certain, just checking).

Also, could you give us more detail than "didn't work"? What happened when you tried this?

[Andrew Thackray]

This code does the trick. It deletes all rows with a number in column A and stops at the first row with an emplty column A

 
Sub DeleteNumericRows()
 
Dim x As Integer
Dim Y As Double
Dim Contents As String
 
On Error Resume Next
x = 1
 
NextRow:
    Contents = Cells(x, 1)
    If Len(Contents) > 0 Then
        Y = CDbl(Contents)
        If Err.Number <> 13 Then
            Rows(x).Delete
            x = x - 1
        End If
    x = x + 1
    Err.Number = 0
    Contents = ""
    GoTo NextRow
    End If
 
End Sub
 

[Eager VBA Learner]

Just a quick question about your code - when you use .Select, doesn't that change the active cell? (I'm not certain, just checking).

Also, could you give us more detail than "didn't work"? What happened when you tried this?

Yes. For instance, if my routing is just:
:
Public Sub FindNumber()
Dim X As Integer
X = 0
X = X + 1
ActiveCell.Offs et(X, 0).Select
Public Sub FindNumber()
Dim X As Integer
X = 0
X = X + 1
ActiveCell.Offs et(X, 0).Select
End Sub
..then the active cell moves down one row in the column each time the macro is run. But when I include the loop, it doesn't move at all. I thought it should keep moving down until it reaches a number greater than zero.





End Sub

[Eager VBA Learner]

This code does the trick. It deletes all rows with a number in column A and stops at the first row with an emplty column A

 
Sub DeleteNumericRows()
 
Dim x As Integer
Dim Y As Double
Dim Contents As String
 
On Error Resume Next
x = 1
 
NextRow:
    Contents = Cells(x, 1)
    If Len(Contents) > 0 Then
        Y = CDbl(Contents)
        If Err.Number <> 13 Then
            Rows(x).Delete
            x = x - 1
        End If
    x = x + 1
    Err.Number = 0
    Contents = ""
    GoTo NextRow
    End If
 
End Sub
 

It works perfectly, Andrew. Many Thanks!