Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
I have the following data and I am trying to make a loop to delete all row
except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Christy,
Check the code below. Instead of using 3300 as the last row, you can determine the last used row to make it more efficient. You also don't need to select the cell to delete the row. But the most important thing is that you need to go through the cells from bottom to top so you don't miss anything. Sub DeleteUnusedRows() Dim i As Integer Dim lLastRow As Long lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If lLastRow < 7 Then Exit Sub For i = lLastRow To 7 Step -1 With Cells(i, 3) If .Text < "Count required?" Then .EntireRow.Delete End With Next i Range("a1").Select End Sub "Christy" wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Sub DeleteUnusedRows()
Dim i As Long For i = 3300 To 7 Step -1 If lcase(Cells(i, 3).Value) < "count required?" Then rows(i).Delete End If Next i Range("a1").Select End Sub -- Regards, Tom Ogilvy "Christy" wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Do you really need to delete them or could you just filter the list?
The problem with your macro is this: column A 1 2 3 4 for i = 1 to 3 debug.print cells(i,3) next i will print: 1 2 3 but for i = 1 to 3 if cells(i,3) = 2 then cells(i,3).entirerow.delete end if debug.print cells(i,3) next i will print: 1 3 4 So if you want to actually check each row then you need to step back one after you perform your delete. Thus the i = i - 1 that I've added. Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete i = i - 1 End If Next i Range("a1").Select End Sub On Feb 22, 10:20 am, Christy wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Here is another possibility
Sub DeleteUnusedrows() columns(3).specialCells(xlconstants,xlNumbers).Ent irerow.delete end Sub or if you want to protect cells above row 7 Sub DeleteRows() dim rng as Range set rng = Range(cells(7,3),cells(rows.count,3).End(xlup)) rng.specialCells(xlConstants,xlNumbers).Entirerow. Delete End Sub these assume all entries in column C that need to be deleted will have a hard coded number in them. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Sub DeleteUnusedRows() Dim i As Long For i = 3300 To 7 Step -1 If lcase(Cells(i, 3).Value) < "count required?" Then rows(i).Delete End If Next i Range("a1").Select End Sub -- Regards, Tom Ogilvy "Christy" wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Perfect -Thanks so much!
"Tom Ogilvy" wrote: Here is another possibility Sub DeleteUnusedrows() columns(3).specialCells(xlconstants,xlNumbers).Ent irerow.delete end Sub or if you want to protect cells above row 7 Sub DeleteRows() dim rng as Range set rng = Range(cells(7,3),cells(rows.count,3).End(xlup)) rng.specialCells(xlConstants,xlNumbers).Entirerow. Delete End Sub these assume all entries in column C that need to be deleted will have a hard coded number in them. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Sub DeleteUnusedRows() Dim i As Long For i = 3300 To 7 Step -1 If lcase(Cells(i, 3).Value) < "count required?" Then rows(i).Delete End If Next i Range("a1").Select End Sub -- Regards, Tom Ogilvy "Christy" wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with loop to delete rows
Thank you for the tip on using the used range - I appreciate the help I
always get from this forum "Vergel Adriano" wrote: Christy, Check the code below. Instead of using 3300 as the last row, you can determine the last used row to make it more efficient. You also don't need to select the cell to delete the row. But the most important thing is that you need to go through the cells from bottom to top so you don't miss anything. Sub DeleteUnusedRows() Dim i As Integer Dim lLastRow As Long lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If lLastRow < 7 Then Exit Sub For i = lLastRow To 7 Step -1 With Cells(i, 3) If .Text < "Count required?" Then .EntireRow.Delete End With Next i Range("a1").Select End Sub "Christy" wrote: I have the following data and I am trying to make a loop to delete all row except the ones that have the words 'Count Required' in column C A B C D UNIT NAME AUDIT WITHIN? 123 Main 0.19 YES 123 Main 0.43 YES 123 Main -13.44 NO 123 Main Count Required? YES 456 Smallville -25.77 NO 456 Smallville 95.76 NO 456 Smallville 23.42 YES 456 Smallville Count Required? YES 789 Anytown 8.04 YES I tried the following loop which only deletes some of the rows. I have to run it seveal times to get what I want. Can anyone tell what I am doing wrong or offer a better way to accomplish my task? Sub DeleteUnusedRows() Dim i As Integer For i = 7 To 3300 Step 1 Cells(i, 3).Select If ActiveCell.Value < "Count required?" Then ActiveCell.EntireRow.Delete End If Next i Range("a1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete Rows in Excel In a Do Loop | Excel Worksheet Functions | |||
Excel loop to delete redudent rows | Excel Programming | |||
Loop all sheetsand delete empty rows | Excel Programming | |||
loop to delete rows... | Excel Programming | |||
How do I delete rows and columns in With With End Loop? | Excel Programming |