Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
I have a spreadsheet with amounts that start in column C and goes through
column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row for i = rw to 9 step -1 if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then rows(rw).Delete end if Next Hopefully your data actually contains numbers and not numbers stored as strings. -- Regards, Tom Ogilvy "TJN" wrote in message ... I have a spreadsheet with amounts that start in column C and goes through column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
Tom -
I punched this in and it deletes only the very last row of data (which happens to sum to zero - I didn't test to see what happens if it doesn't sum to zero). I was messing around with an Offset command to move up a row after the if..then statement, but I can't seem to get it to work. Am I missing something? Thanks for your help. Tim "Tom Ogilvy" wrote: Dim rw as Long, i as Long rw = cells(rows.count,1).End(xlup).Row for i = rw to 9 step -1 if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then rows(rw).Delete end if Next Hopefully your data actually contains numbers and not numbers stored as strings. -- Regards, Tom Ogilvy "TJN" wrote in message ... I have a spreadsheet with amounts that start in column C and goes through column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
A couple of typos - try this which worked in my test:
Sub ABCD() Dim rw As Long, i As Long rw = Cells(Rows.Count, 1).End(xlUp).Row For i = rw To 9 Step -1 If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then Rows(i).Delete End If Next End Sub -- regards, Tom Ogilvy "TJN" wrote in message ... Tom - I punched this in and it deletes only the very last row of data (which happens to sum to zero - I didn't test to see what happens if it doesn't sum to zero). I was messing around with an Offset command to move up a row after the if..then statement, but I can't seem to get it to work. Am I missing something? Thanks for your help. Tim "Tom Ogilvy" wrote: Dim rw as Long, i as Long rw = cells(rows.count,1).End(xlup).Row for i = rw to 9 step -1 if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then rows(rw).Delete end if Next Hopefully your data actually contains numbers and not numbers stored as strings. -- Regards, Tom Ogilvy "TJN" wrote in message ... I have a spreadsheet with amounts that start in column C and goes through column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
Tom -
Thanks. The macro works great. I appreciate your help. Tim "Tom Ogilvy" wrote: A couple of typos - try this which worked in my test: Sub ABCD() Dim rw As Long, i As Long rw = Cells(Rows.Count, 1).End(xlUp).Row For i = rw To 9 Step -1 If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then Rows(i).Delete End If Next End Sub -- regards, Tom Ogilvy "TJN" wrote in message ... Tom - I punched this in and it deletes only the very last row of data (which happens to sum to zero - I didn't test to see what happens if it doesn't sum to zero). I was messing around with an Offset command to move up a row after the if..then statement, but I can't seem to get it to work. Am I missing something? Thanks for your help. Tim "Tom Ogilvy" wrote: Dim rw as Long, i as Long rw = cells(rows.count,1).End(xlup).Row for i = rw to 9 step -1 if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then rows(rw).Delete end if Next Hopefully your data actually contains numbers and not numbers stored as strings. -- Regards, Tom Ogilvy "TJN" wrote in message ... I have a spreadsheet with amounts that start in column C and goes through column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If sum = 0, delete row
Hi TJN,
I would like to know the step-by-step to use this macro as I need it too. Do you mind telling me? Thanks, sorry for the trouble as I am total beginner in macros. Sharon "TJN" wrote: Tom - Thanks. The macro works great. I appreciate your help. Tim "Tom Ogilvy" wrote: A couple of typos - try this which worked in my test: Sub ABCD() Dim rw As Long, i As Long rw = Cells(Rows.Count, 1).End(xlUp).Row For i = rw To 9 Step -1 If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then Rows(i).Delete End If Next End Sub -- regards, Tom Ogilvy "TJN" wrote in message ... Tom - I punched this in and it deletes only the very last row of data (which happens to sum to zero - I didn't test to see what happens if it doesn't sum to zero). I was messing around with an Offset command to move up a row after the if..then statement, but I can't seem to get it to work. Am I missing something? Thanks for your help. Tim "Tom Ogilvy" wrote: Dim rw as Long, i as Long rw = cells(rows.count,1).End(xlup).Row for i = rw to 9 step -1 if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then rows(rw).Delete end if Next Hopefully your data actually contains numbers and not numbers stored as strings. -- Regards, Tom Ogilvy "TJN" wrote in message ... I have a spreadsheet with amounts that start in column C and goes through column AE (columns A & B contain an entity number and an entity name). The number of rows varies, but it is typically about 600 rows of data. This data begins on row 9. Row 8 contains the column headers and the 1st 7 rows contain junk that comes over from the program I download this data from. It can be eliminated if it helps. Basically I am looking for a macro that steps through each row of data and if the sum of column C through column AE = zero, I would like the entire row to be deleted. Does anybody have any ideas? I feel bad because this is the third time I have come to this group for help. Each time the solutions have been terriffic. I keep hoping I can answer a question instead of always asking, but so far all the questions asked have been above my skill level. Thanks, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |