Number format as condition for not deleting a line

M

mjwillyone

Dear Friends,

I am having a problem with a particular macro I am working on. The
following macro is supposed to delete all rows that do not have a
number in column A. The user enters the number of rows to process,
then lets the macro do its job. Unfortately, I end up with all of the
rows being deleted.

I have checked my data on the test page and I have two rows that
contain numbers in column A. I have verified that these are numbers
by right-clicking over these cells and choosing cell-format - both are
listed as "number".

Here is the code that I have created . . .

Counter = InputBox("Enter the total number of rows to process")

Worksheets("sheet1").Activate
Range("A1").Select
For i = 1 To Counter
If ActiveCell.NumberFormat = "Number" Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Delete
Counter = Counter - 1
Else
Selection.EntireRow.Delete
End If
Next i

Thank you for your help!

Mike
 
K

Ken Wright

What is the criteria for determining that a row can be deleted? is it that the
cell for that row in Col A is empty / contains a number / does not contain a
number? Can the cell contain things other than a number? Give us the full
facts and we'll do what we can for you.
 
D

Dave Peterson

How about:

Option Explicit
Sub testme02()

Dim Counter As Long
Dim i As Long
Dim delRng As Range

Counter = Application.InputBox _
("Enter the total number of rows to process", Type:=1)

If Counter < 1 Then
Exit Sub
End If

With Worksheets("sheet1")
For i = 1 To Counter
If Application.IsNumber(.Cells(i, 1).Value) = False Then
If delRng Is Nothing Then
Set delRng = .Cells(i, 1)
Else
Set delRng = Union(.Cells(i, 1), delRng)
End If
End If
Next i
End With

If delRng Is Nothing Then
'nothing to do
Else
delRng.EntireRow.Delete
End If

End Sub
 
M

mjwillyone

Gentlemen,

Actually, here is a routine I have that works great to delete all row
where the word "Posted" is not found in cell "B" of a row. All
needed to do was tweak this so that is would not look at cell "A" of
row, determine if the value in cell "A" is a number and delete the ro
if it is not.

I know that I would need to change the range to "A1" but do not kno
what I have to do with the 'If ActiveCell = "Posted" Then' text t
get it to look at the numeric cell attribute of the activecell.

Worksheets("sheet1").Activate
Range("B1").Select

Counter = InputBox("Enter the total number of rows to process")

For i = 1 To Counter
If ActiveCell = "Posted" Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Delete
Counter = Counter - 1
Else
Selection.EntireRow.Delete
End If
Next i


Thanks,
Mik
 
M

mjwillyone

Gentlemen,

The third row of the reply I just sent should read as follows:

would now (instead of not) look at cell "A" of a row, determine if the
value in cell "A" is a number and delete

Sorry,
Mike
 
D

Dave Peterson

If application.isnumber(activecell.value) then

would check to see if the activecell were numeric.

But your original code actually deletes the row under the cell with posted.

And I'd suggest that you start from the bottom and work upwards if you don't
want to use the routine I posted.
 
M

mjwillyone

Dave,

Thank you very much! Your single-line code worked great!

Happy New Year,
Mike
 
Top