Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
something like this should work
with sheet1 for i=5 to 1 step -1 if cells(i,"a")0 then rows(i).delete end with -- Don Guillett SalesAid Software "marthasanchez" wrote in message ... How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
You might also want to try
.... Sheet1.Rows(i).Delete Shift:=xlUp HTH -- Allllen "marthasanchez" wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Explanation:
Cells(row,column) You have Cells(row, i), so i represents your column. You just have to put it the other way around to sift through rows. Solution: Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select '<-You probably don't need this line of code For i = 2 To 6 '<-Are you sure it is to 6 and not 5? Cells(i, 2).Select '<-CHANGED, but also try it without this line of code If Sheet1.Cells(i, 2) 0 Then Sheet1.Rows(i).Delete '<-CHANGED Next i End Sub -- Allllen "marthasanchez" wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Oops forgot "End With"
Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End With End Sub Charles Die_Another_Day wrote: Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Thank-you!
Quick question "Allllen" wrote: Explanation: Cells(row,column) You have Cells(row, i), so i represents your column. You just have to put it the other way around to sift through rows. Solution: Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select '<-You probably don't need this line of code For i = 2 To 6 '<-Are you sure it is to 6 and not 5? Cells(i, 2).Select '<-CHANGED, but also try it without this line of code If Sheet1.Cells(i, 2) 0 Then Sheet1.Rows(i).Delete '<-CHANGED Next i End Sub -- Allllen "marthasanchez" wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
OK. Maybe I was a bit hasty.... So solly
-- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... also Don's had no period in front of the cells or rows, so it would leave the impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Thank-you for the awesome feedback. I have read that the best way to sifr
through the data is from the end of the recordset to the beggining. How do I do this if the number in the recordset changes each time the program is run? "Tom Ogilvy" wrote: also Don's had no period in front of the cells or rows, so it would leave the impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Martha, Assuming that you have a Column in which you KNOW has data in
the last used row... Dim LastRow As Long With Sheets("Sheet1") LastRow = .Range("B" & .Rows.Count).End(xlUp).Row End With Charles marthasanchez wrote: Thank-you for the awesome feedback. I have read that the best way to sifr through the data is from the end of the recordset to the beggining. How do I do this if the number in the recordset changes each time the program is run? "Tom Ogilvy" wrote: also Don's had no period in front of the cells or rows, so it would leave the impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
OK...dont mean to sound slow....but, so how would i merge this code and the
previous code into one? Dim LastRow As Long Dim i as interger With Sheets("Sheet1") LastRow = .Range("B" & .Rows.Count).End(xlUp).Row cells (i,2).select if sheet1.cells (i,2).0 then sheet1.rows (i).delete? End With exit sub? "Die_Another_Day" wrote: Martha, Assuming that you have a Column in which you KNOW has data in the last used row... Dim LastRow As Long With Sheets("Sheet1") LastRow = .Range("B" & .Rows.Count).End(xlUp).Row End With Charles marthasanchez wrote: Thank-you for the awesome feedback. I have read that the best way to sifr through the data is from the end of the recordset to the beggining. How do I do this if the number in the recordset changes each time the program is run? "Tom Ogilvy" wrote: also Don's had no period in front of the cells or rows, so it would leave the impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete entire row with a positive number
Sub click()
Dim cnt as Long Dim LastRow As Long With Sheet1 LastRow = .Range("B" & .Rows.Count).End(xlUp).Row For cnt = LastRow to 2 Step -1 If .Range("B" & cnt) 0 Then .Rows(i).Delete Next End With End Sub That should get you going. Let me know if you need more help. Charles marthasanchez wrote: OK...dont mean to sound slow....but, so how would i merge this code and the previous code into one? Dim LastRow As Long Dim i as interger With Sheets("Sheet1") LastRow = .Range("B" & .Rows.Count).End(xlUp).Row cells (i,2).select if sheet1.cells (i,2).0 then sheet1.rows (i).delete? End With exit sub? "Die_Another_Day" wrote: Martha, Assuming that you have a Column in which you KNOW has data in the last used row... Dim LastRow As Long With Sheets("Sheet1") LastRow = .Range("B" & .Rows.Count).End(xlUp).Row End With Charles marthasanchez wrote: Thank-you for the awesome feedback. I have read that the best way to sifr through the data is from the end of the recordset to the beggining. How do I do this if the number in the recordset changes each time the program is run? "Tom Ogilvy" wrote: also Don's had no period in front of the cells or rows, so it would leave the impression of working on Sheet1, but actually worked on the activesheet or if in a sheet module, on the sheet containing the code. No problem if the activesheet is sheet1 or sheet1 contains the code, but then you don't need the with. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Close, yours would error out as you forgot the next statement, although that wasn't my point for posting, I was trying to show th OP what was wrong with her code and how to correct it. Charles Don Guillett wrote: Isn't that the same as my post? -- Don Guillett SalesAid Software "Die_Another_Day" wrote in message oups.com... Martha, the main problem is that as you delete a row, you end up skipping the next row, for example if B2 = 5, and B3 = 5, first you delete row 2, now B2 holds the value that was in B3 and you said to go to "Next i" which means the original value from B3 never gets checked. Also the syntax for the Cells object is backwards, Cells(2,i) where i = 6 refers to Row 2, Column 6. The best approach to deleting rows is to move backwards through them: Sub click() Dim i as Integer With Sheet1 For i = 6 to 2 Step -1 If .Range("B" & i) 0 Then .Rows(i).Delete Next End Sub HTH Charles Chickering marthasanchez wrote: How do I delete an enitre row based on one columns number. For example, if column A has a 5 then the entire row should be deleted and the formula move to the next row. This is what i have, but it is siftong through columns. Sub click() Dim i As Integer Sheet1.Select Sheet1.Range("a2:b5").Select For i = 2 To 6 Cells(2, i).Select If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete Next i End Sub PLEASE HELP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I subtract a negative number from a positive number? | Excel Discussion (Misc queries) | |||
Subtracting positive amts from negative and positive from positive | Excel Worksheet Functions | |||
converting a positive number to neg number in spreadsheets | Excel Discussion (Misc queries) | |||
locate positive numbers and delete rows containing | Excel Discussion (Misc queries) | |||
Delete Entire Row in Excel if specific column does not contain a 7-digit number | Excel Programming |