Number format as condition for not deleting a line
 

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


---
Message posted from http://www.ExcelForum.com/

Number format as condition for not deleting a line
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all :-)
----------------------------------------------------------------------------



"mjwillyone" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003

Number format as condition for not deleting a line
 

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

mjwillyone wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Number format as condition for not deleting a line
 

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

--
Message posted from http://www.ExcelForum.com

Number format as condition for not deleting a line
 

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


---
Message posted from http://www.ExcelForum.com/

Number format as condition for not deleting a line
 

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.




mjwillyone wrote:

Gentlemen,

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

I know that I would need to change the range to "A1" but do not know
what I have to do with the 'If ActiveCell = "Posted" Then' text to
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,
Mike

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Number format as condition for not deleting a line
 

Dave,

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

Happy New Year,
Mike


---
Message posted from http://www.ExcelForum.com/