Delete rows with numeric values, leave rows with text
 

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.

Delete rows with numeric values, leave rows with text
 

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

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

Delete rows with numeric values, leave rows with text
 

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
'<<=================

---
Regards,
Norman



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

Delete rows with numeric values, leave rows with text
 

"GSpline" wrote:

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.

Delete rows with numeric values, leave rows with text
 

Both responses worked, thank you very much.



"GSpline" wrote:

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.

Delete rows with numeric values, leave rows with text
 

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?



"PY & Associates" wrote:

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

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