Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN | Excel Discussion (Misc queries) | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Delete Rows where there are #N/A Values | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |