Delete rows with numeric values, leave rows with text

GSpline · 10-07-2005, 08:05 PM

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 · 10-07-2005, 08:05 PM

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

"GSpline" <[email protected]> wrote in message
news:[email protected]...
> 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.

Norman Jones · 10-07-2005, 09:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
>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.

GSpline · 10-10-2005, 02:05 PM

"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.

GSpline · 10-10-2005, 02:05 PM

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.

GSpline · 10-10-2005, 08:05 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>