Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 145
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN ramesh k. goyal - abohar[_2_] Excel Discussion (Misc queries) 1 October 28th 09 07:50 AM
How to Delete empty rows in excel in b/w rows with values Dennis Excel Worksheet Functions 3 August 28th 07 04:15 PM
Return Matched Numeric Values across Rows Sam via OfficeKB.com Excel Worksheet Functions 2 January 3rd 07 12:03 AM
Delete Rows where there are #N/A Values FIRSTROUNDKO via OfficeKB.com Excel Worksheet Functions 3 August 3rd 06 04:03 PM
delete empty rows between rows with text Paulo Baptista Excel Discussion (Misc queries) 2 February 28th 05 04:41 PM


All times are GMT +1. The time now is 10:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"