Delete rows with numeric values, leave rows with text

[Guest]

I am trying to delete any row in a worksheet that has a numeric value in a
specific column (assume column A) while leaving any row intact that contains
a text value in that same column. I was able to get numeric values to
delete, but the text rows would also delete... leaving no data at all because
all of it was deleted along with the numeric. I saw another post (number
less than 100) and was able to use that code to some extent, but I am not
sure how to modify it to affect only numeric & skip text. Here is the code
from that post, submitted by David:

Sub Macro1()
Range("I1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value < 100 Then
z = ActiveCell.Row
Rows(z).Delete
ActiveCell.Offset(-1, 0).Select
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

and here is how I modified it:

Sub Macro1()
Range("A2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value >= 0 Then
z = ActiveCell.Row
Rows(z).Delete
ActiveCell.Offset(-1, 0).Select
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub


I purposely want to start the macro in cell A2 and continue to the end of
the data at the bottom of that column (it will vary). I am about to go nutty
with this one, any help would be appreciated.

Thanks.

 

[PY & Associates]

change if activecell.value<100 then
to if isnumeric(activecell) then

 

[Norman Jones]

Hi G,

Try:
'=================>>
Public Sub Tester01()
Dim rng As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
Set rng = SH.Columns(1).SpecialCells(xlConstants, xlNumbers)

rng.EntireRow.Delete

End Sub
'<<=================

 

[Guest]

I am trying to delete any row in a worksheet that has a numeric value in a
specific column (assume column A) while leaving any row intact that contains
a text value in that same column. I was able to get numeric values to
delete, but the text rows would also delete... leaving no data at all because
all of it was deleted along with the numeric. I saw another post (number
less than 100) and was able to use that code to some extent, but I am not
sure how to modify it to affect only numeric & skip text. Here is the code
from that post, submitted by David:

Sub Macro1()
Range("I1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value < 100 Then
z = ActiveCell.Row
Rows(z).Delete
ActiveCell.Offset(-1, 0).Select
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

and here is how I modified it:

Sub Macro1()
Range("A2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value >= 0 Then
z = ActiveCell.Row
Rows(z).Delete
ActiveCell.Offset(-1, 0).Select
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub


I purposely want to start the macro in cell A2 and continue to the end of
the data at the bottom of that column (it will vary). I am about to go nutty
with this one, any help would be appreciated.

Thanks.

 

[Guest]

Both responses worked, thank you very much.

 

[Guest]

This method seems to work fine after implementation, however, now I get a row
of "(blank)" entries as the very last row in my pivot table. I cannot figure
out a way to prevent this from happening. Can anyone explain exactly what
causes this, and does anyone have a code solution or ideas to prevent this
from appearing?