| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting non-numeric rows
Can anyone help me? I'm trying to have a macro look in column A and delete
any non-numeric rows (ie... total, Net o, Net I, 76420) from the data. Basically, I just need the rows that have numbers in column A (less 76420). Then I need to combine all of the the 52270 lines into one row. Data: 52200 17625.9 7500 14924.68 3334 -1778 2762 52201 0 0 0 -0.62 8795 3750 52203 0 0 0 0 0 0 52204 10508.34 3950 10666.68 0.34 0 0 52206 0 0 0 0.36 533 80 52207 4611.05 1833 5322.46 665.34 193 55 52217 2045.8 315 17.1 -2.7 90 119 52261 -496 -8016 0 75 553 743 52000 0 0 0 0 0 0 Total 34295.09 5582 30930.92 4071.72 8386 7509 52270 -1583.32 0 0 0 0 0 52270 0 0 0 0 0 0 52270 -1951.26 0 0 0 0 0 52270 19044.54 0 0 0 0 0 52270 5952.71 0 0 0 0 0 52270 3959.57 0 0 0 0 0 52270 7582.63 0 0 0 0 0 52270 14701.37 0 0 0 0 0 52270 3544.5 0 0 0 0 0 52270 615.1 0 0 0 0 0 Total 18860.97 0 0 0 0 0 Total 51865.84 0 0 0 0 0 Total 86160.93 5582 30930.92 4071.72 8386 7509 60440 820.23 0 0 0 0 0 63120 1050.97 0 400 0 0 0 63410 0 0 0 0 0 0 65182 500 0 0 0 0 0 65310 4115.33 0 0 0 0 0 65510 0 0 0 0 0 0 65720 340 0 0 350 5 0 Total 6826.53 0 400 350 5 0 76410 5534.81 0 0 0 0 0 Total 98522.27 5582 31330.92 4421.72 8391 7509 Net O -98522.27 -5582 -31330.92 -4421.72 -8391 -7509 76420 302000 0 0 0 0 0 Total 302000 0 0 0 0 0 Net O 302000 0 0 0 0 0 Net I 203477.73 -5582 -31330.92 -4421.72 -8391 -7509 I wrote the following code but it isn't completly working. It's still leaves one row that is labeled as "total" (it might be a formating situation). I'm also not sure of the most efficient way to add up all of the 52270 rows into one. I know that my code might not be the most efficient, but I don't have a programming background. I also seem to have trouble getting mutliple criteria IF statements to work on my computer at work. Do While ActiveCell.Value "" If ActiveCell.Text = "Total" Then Selection.EntireRow.Delete End If If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net O" Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net I" Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop any help would be appreciated. -- Regards, timmulla |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
deleting non-numeric rows
The easiest way is probably to sort the data by column A:
The numbers will go to the top and text will go to the bottom. Find the first row containing text (many ways to do this) and delete all rows to the end. You should be able to designate all rows with text and delete them with a single statement. Write back if you need further help... -- steveB Remove "AYN" from email to respond "timmulla" wrote in message ... Can anyone help me? I'm trying to have a macro look in column A and delete any non-numeric rows (ie... total, Net o, Net I, 76420) from the data. Basically, I just need the rows that have numbers in column A (less 76420). Then I need to combine all of the the 52270 lines into one row. Data: 52200 17625.9 7500 14924.68 3334 -1778 2762 52201 0 0 0 -0.62 8795 3750 52203 0 0 0 0 0 0 52204 10508.34 3950 10666.68 0.34 0 0 52206 0 0 0 0.36 533 80 52207 4611.05 1833 5322.46 665.34 193 55 52217 2045.8 315 17.1 -2.7 90 119 52261 -496 -8016 0 75 553 743 52000 0 0 0 0 0 0 Total 34295.09 5582 30930.92 4071.72 8386 7509 52270 -1583.32 0 0 0 0 0 52270 0 0 0 0 0 0 52270 -1951.26 0 0 0 0 0 52270 19044.54 0 0 0 0 0 52270 5952.71 0 0 0 0 0 52270 3959.57 0 0 0 0 0 52270 7582.63 0 0 0 0 0 52270 14701.37 0 0 0 0 0 52270 3544.5 0 0 0 0 0 52270 615.1 0 0 0 0 0 Total 18860.97 0 0 0 0 0 Total 51865.84 0 0 0 0 0 Total 86160.93 5582 30930.92 4071.72 8386 7509 60440 820.23 0 0 0 0 0 63120 1050.97 0 400 0 0 0 63410 0 0 0 0 0 0 65182 500 0 0 0 0 0 65310 4115.33 0 0 0 0 0 65510 0 0 0 0 0 0 65720 340 0 0 350 5 0 Total 6826.53 0 400 350 5 0 76410 5534.81 0 0 0 0 0 Total 98522.27 5582 31330.92 4421.72 8391 7509 Net O -98522.27 -5582 -31330.92 -4421.72 -8391 -7509 76420 302000 0 0 0 0 0 Total 302000 0 0 0 0 0 Net O 302000 0 0 0 0 0 Net I 203477.73 -5582 -31330.92 -4421.72 -8391 -7509 I wrote the following code but it isn't completly working. It's still leaves one row that is labeled as "total" (it might be a formating situation). I'm also not sure of the most efficient way to add up all of the 52270 rows into one. I know that my code might not be the most efficient, but I don't have a programming background. I also seem to have trouble getting mutliple criteria IF statements to work on my computer at work. Do While ActiveCell.Value "" If ActiveCell.Text = "Total" Then Selection.EntireRow.Delete End If If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net O" Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net I" Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop any help would be appreciated. -- Regards, timmulla |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
deleting non-numeric rows
Hi Timmulla,
It is not clear what you are trying to achieve. You say that you want to delete all rows which have a non-numeric value in column A and, if so, try: '======================= Public Sub Tester01() Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete End Sub '<<======================= However in your code, I see: If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete This would seem to be at variance with your stated objective. To assist further, some elucidation is required. --- Regards, Norman "timmulla" wrote in message ... Can anyone help me? I'm trying to have a macro look in column A and delete any non-numeric rows (ie... total, Net o, Net I, 76420) from the data. Basically, I just need the rows that have numbers in column A (less 76420). Then I need to combine all of the the 52270 lines into one row. Data: 52200 17625.9 7500 14924.68 3334 -1778 2762 52201 0 0 0 -0.62 8795 3750 52203 0 0 0 0 0 0 52204 10508.34 3950 10666.68 0.34 0 0 52206 0 0 0 0.36 533 80 52207 4611.05 1833 5322.46 665.34 193 55 52217 2045.8 315 17.1 -2.7 90 119 52261 -496 -8016 0 75 553 743 52000 0 0 0 0 0 0 Total 34295.09 5582 30930.92 4071.72 8386 7509 52270 -1583.32 0 0 0 0 0 52270 0 0 0 0 0 0 52270 -1951.26 0 0 0 0 0 52270 19044.54 0 0 0 0 0 52270 5952.71 0 0 0 0 0 52270 3959.57 0 0 0 0 0 52270 7582.63 0 0 0 0 0 52270 14701.37 0 0 0 0 0 52270 3544.5 0 0 0 0 0 52270 615.1 0 0 0 0 0 Total 18860.97 0 0 0 0 0 Total 51865.84 0 0 0 0 0 Total 86160.93 5582 30930.92 4071.72 8386 7509 60440 820.23 0 0 0 0 0 63120 1050.97 0 400 0 0 0 63410 0 0 0 0 0 0 65182 500 0 0 0 0 0 65310 4115.33 0 0 0 0 0 65510 0 0 0 0 0 0 65720 340 0 0 350 5 0 Total 6826.53 0 400 350 5 0 76410 5534.81 0 0 0 0 0 Total 98522.27 5582 31330.92 4421.72 8391 7509 Net O -98522.27 -5582 -31330.92 -4421.72 -8391 -7509 76420 302000 0 0 0 0 0 Total 302000 0 0 0 0 0 Net O 302000 0 0 0 0 0 Net I 203477.73 -5582 -31330.92 -4421.72 -8391 -7509 I wrote the following code but it isn't completly working. It's still leaves one row that is labeled as "total" (it might be a formating situation). I'm also not sure of the most efficient way to add up all of the 52270 rows into one. I know that my code might not be the most efficient, but I don't have a programming background. I also seem to have trouble getting mutliple criteria IF statements to work on my computer at work. Do While ActiveCell.Value "" If ActiveCell.Text = "Total" Then Selection.EntireRow.Delete End If If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net O" Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net I" Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop any help would be appreciated. -- Regards, timmulla |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
deleting non-numeric rows
Hi Timmulla
'======================= Public Sub Tester01() Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete End Sub '<<======================= In case Column A contains no constant values, replace the above with: '======================= Public Sub Tester01() On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= --- Regards, Norman "Norman Jones" wrote in message ... Hi Timmulla, It is not clear what you are trying to achieve. You say that you want to delete all rows which have a non-numeric value in column A and, if so, try: '======================= Public Sub Tester01() Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete End Sub '<<======================= However in your code, I see: If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete This would seem to be at variance with your stated objective. To assist further, some elucidation is required. --- Regards, Norman "timmulla" wrote in message ... Can anyone help me? I'm trying to have a macro look in column A and delete any non-numeric rows (ie... total, Net o, Net I, 76420) from the data. Basically, I just need the rows that have numbers in column A (less 76420). Then I need to combine all of the the 52270 lines into one row. Data: 52200 17625.9 7500 14924.68 3334 -1778 2762 52201 0 0 0 -0.62 8795 3750 52203 0 0 0 0 0 0 52204 10508.34 3950 10666.68 0.34 0 0 52206 0 0 0 0.36 533 80 52207 4611.05 1833 5322.46 665.34 193 55 52217 2045.8 315 17.1 -2.7 90 119 52261 -496 -8016 0 75 553 743 52000 0 0 0 0 0 0 Total 34295.09 5582 30930.92 4071.72 8386 7509 52270 -1583.32 0 0 0 0 0 52270 0 0 0 0 0 0 52270 -1951.26 0 0 0 0 0 52270 19044.54 0 0 0 0 0 52270 5952.71 0 0 0 0 0 52270 3959.57 0 0 0 0 0 52270 7582.63 0 0 0 0 0 52270 14701.37 0 0 0 0 0 52270 3544.5 0 0 0 0 0 52270 615.1 0 0 0 0 0 Total 18860.97 0 0 0 0 0 Total 51865.84 0 0 0 0 0 Total 86160.93 5582 30930.92 4071.72 8386 7509 60440 820.23 0 0 0 0 0 63120 1050.97 0 400 0 0 0 63410 0 0 0 0 0 0 65182 500 0 0 0 0 0 65310 4115.33 0 0 0 0 0 65510 0 0 0 0 0 0 65720 340 0 0 350 5 0 Total 6826.53 0 400 350 5 0 76410 5534.81 0 0 0 0 0 Total 98522.27 5582 31330.92 4421.72 8391 7509 Net O -98522.27 -5582 -31330.92 -4421.72 -8391 -7509 76420 302000 0 0 0 0 0 Total 302000 0 0 0 0 0 Net O 302000 0 0 0 0 0 Net I 203477.73 -5582 -31330.92 -4421.72 -8391 -7509 I wrote the following code but it isn't completly working. It's still leaves one row that is labeled as "total" (it might be a formating situation). I'm also not sure of the most efficient way to add up all of the 52270 rows into one. I know that my code might not be the most efficient, but I don't have a programming background. I also seem to have trouble getting mutliple criteria IF statements to work on my computer at work. Do While ActiveCell.Value "" If ActiveCell.Text = "Total" Then Selection.EntireRow.Delete End If If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net O" Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net I" Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop any help would be appreciated. -- Regards, timmulla |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
deleting non-numeric rows
Hi Norman,
Thanks for your help. I will try your code out later today. I do need to delete all non-numeric rows based on column (A:A), but I also need to delete the row labeled "76420" as well. -- Regards, timmulla "Norman Jones" wrote: Hi Timmulla '======================= Public Sub Tester01() Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete End Sub '<<======================= In case Column A contains no constant values, replace the above with: '======================= Public Sub Tester01() On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete On Error GoTo 0 End Sub '<<======================= --- Regards, Norman "Norman Jones" wrote in message ... Hi Timmulla, It is not clear what you are trying to achieve. You say that you want to delete all rows which have a non-numeric value in column A and, if so, try: '======================= Public Sub Tester01() Columns("A:A").SpecialCells(xlCellTypeConstants, 2). _ EntireRow.Delete End Sub '<<======================= However in your code, I see: If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete This would seem to be at variance with your stated objective. To assist further, some elucidation is required. --- Regards, Norman "timmulla" wrote in message ... Can anyone help me? I'm trying to have a macro look in column A and delete any non-numeric rows (ie... total, Net o, Net I, 76420) from the data. Basically, I just need the rows that have numbers in column A (less 76420). Then I need to combine all of the the 52270 lines into one row. Data: 52200 17625.9 7500 14924.68 3334 -1778 2762 52201 0 0 0 -0.62 8795 3750 52203 0 0 0 0 0 0 52204 10508.34 3950 10666.68 0.34 0 0 52206 0 0 0 0.36 533 80 52207 4611.05 1833 5322.46 665.34 193 55 52217 2045.8 315 17.1 -2.7 90 119 52261 -496 -8016 0 75 553 743 52000 0 0 0 0 0 0 Total 34295.09 5582 30930.92 4071.72 8386 7509 52270 -1583.32 0 0 0 0 0 52270 0 0 0 0 0 0 52270 -1951.26 0 0 0 0 0 52270 19044.54 0 0 0 0 0 52270 5952.71 0 0 0 0 0 52270 3959.57 0 0 0 0 0 52270 7582.63 0 0 0 0 0 52270 14701.37 0 0 0 0 0 52270 3544.5 0 0 0 0 0 52270 615.1 0 0 0 0 0 Total 18860.97 0 0 0 0 0 Total 51865.84 0 0 0 0 0 Total 86160.93 5582 30930.92 4071.72 8386 7509 60440 820.23 0 0 0 0 0 63120 1050.97 0 400 0 0 0 63410 0 0 0 0 0 0 65182 500 0 0 0 0 0 65310 4115.33 0 0 0 0 0 65510 0 0 0 0 0 0 65720 340 0 0 350 5 0 Total 6826.53 0 400 350 5 0 76410 5534.81 0 0 0 0 0 Total 98522.27 5582 31330.92 4421.72 8391 7509 Net O -98522.27 -5582 -31330.92 -4421.72 -8391 -7509 76420 302000 0 0 0 0 0 Total 302000 0 0 0 0 0 Net O 302000 0 0 0 0 0 Net I 203477.73 -5582 -31330.92 -4421.72 -8391 -7509 I wrote the following code but it isn't completly working. It's still leaves one row that is labeled as "total" (it might be a formating situation). I'm also not sure of the most efficient way to add up all of the 52270 rows into one. I know that my code might not be the most efficient, but I don't have a programming background. I also seem to have trouble getting mutliple criteria IF statements to work on my computer at work. Do While ActiveCell.Value "" If ActiveCell.Text = "Total" Then Selection.EntireRow.Delete End If If ActiveCell.Value = 76420 Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net O" Then Selection.EntireRow.Delete End If If ActiveCell.Text = "Net I" Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop any help would be appreciated. -- Regards, timmulla |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
| Deleting non numeric characters. | Excel Programming | |||
Linear Mode
