|
How to delete correct # of rows
I want to use a macro to clean up a file and save time. The Requests Found
field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
Hi Krystal,
I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
Thanks for the looping code. Any idea on how I can delete the correct number
of rows/records....? "Carlos Mallen" wrote: Hi Krystal, I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
It sounds like you have two different questions.
(1) How do I loop through the correct number of rows Add this function to your code module: Function Find_Last(sht As Worksheet) Find_Last = sht.Cells.Find(What:="*", After:=sht.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row End Function Then within your sub, call it before your loop: Sub whatever MyRowCount = Find_Last(Sheet1) For i = 1 to MyRowCount 'do stuff Next i End Sub (2) I'm not entirely clear on your second question- are you trying to identify the maximum number in Column B associated with the value in Column A? Probably not, because your example values in column B don't increment with each entry. Are you trying to count the number of times a value in column A is repeated? Or just return the value from Column B once? Post back with some clarification, and hopefully we can get you started. HTH, Keith "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
How do you know what the correct number to delete is? Are you just
trying to eliminate any duplications? On Oct 12, 2:40*pm, Krystal Peters wrote: Thanks for the looping code. *Any idea on how I can delete the correct number of rows/records....? "Carlos Mallen" wrote: Hi Krystal, I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m * * Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. *The Requests Found field is the maximum # of records that can be deleted. *So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. * I assume I will need to loop through the rows - any suggestion would be appreaciated. *Thanks, Krystal SAMPLE DATA: ACCT_NO * *Requests Found 289278995 * * * * *1 289278999 * * * * *1 289278999 * * * * *1 289278999 * * * * *1 870587008 * * * * *1 119387014 * * * * *1 158675527 * * 2 654375649 * * 2 569777245 * * 2 752478468 * * 2 752478468 * * 2 396378512 * * 2 396378512 * * 2 396378512 * * 2 396378512 * * 2 399778090 * * 3 399778090 * * 3 399778090 * * 3 208777882 * * 4 208777882 * * 4 208777882 * * 4 208777882 * * 4 987178737 * * 4 987178737 * * 4 117468837 * * 7 117468837 * * 7 117468837 * * 7 117468837 * * 7- Hide quoted text - - Show quoted text - |
How to delete correct # of rows
Sure, you just say wks.Rows(RowNumber).Delete. That's it. I still don't get
what does the "correct number" of rows means. -- Carlos Mallen "Krystal Peters" wrote: Thanks for the looping code. Any idea on how I can delete the correct number of rows/records....? "Carlos Mallen" wrote: Hi Krystal, I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
For (2) Col A does not increment as they are acct #'s; Col B let me know the
maximum # of rows I can delete. Below I showed in a third col what i would like to happen... SAMPLE DATA: ACCT_NO Requests Found action to take 289278995 1 delete 289278999 1 keep 289278999 1 keep 289278999 1 delete 870587008 1 delete 119387014 1 delete 158675527 2 delete 654375649 2 delete 569777245 2 delete 752478468 2 delete 752478468 2 delete 396378512 2 delete 396378512 2 delete 396378512 2 keep 396378512 2 keep 399778090 3 delete 399778090 3 delete 399778090 3 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 keep 987178737 4 delete 987178737 4 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete |
How to delete correct # of rows
Hi Krystal
What is the criteria for deleting the record? Or, What is the criteria for keeping the record? You can use either to do the job, but it has to be in an isolated field (row or column) to be able to use it in the loop. "Krystal Peters" wrote in message ... Thanks for the looping code. Any idea on how I can delete the correct number of rows/records....? "Carlos Mallen" wrote: Hi Krystal, I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
The Request Found column has the following formula:
=COUNTIF(Paste_Accounts!A:A,E1117) Paste_Accounts sheet is a list of possible dup or void requests A txt file (different file) is imported into another shee and the last col has the formula above. All requests that equal 0 are kept. Anything more than 0 is deleted base on the # the formula gives. So, if an account # is there twice but the formula results are 1, 1 record is deleted the other is kept. does this help any??? "Carlos Mallen" wrote: Sure, you just say wks.Rows(RowNumber).Delete. That's it. I still don't get what does the "correct number" of rows means. -- Carlos Mallen "Krystal Peters" wrote: Thanks for the looping code. Any idea on how I can delete the correct number of rows/records....? "Carlos Mallen" wrote: Hi Krystal, I don't really understand what you want to do, but here is a macro for looping through a range of cells. Option Explicit Public Sub LoopThrough() Dim i As Long Dim m As Long Dim wks As Worksheet Dim wksName As String wksName = "Sheet1" 'for example Set wks = ActiveWorkbook.Worksheets(wksName) m = 10 'for example For i = 1 To m Debug.Print wks.Cells(i, 1) 'Print in the immediate window the value of each cell Next i Set wks = Nothing End Sub -- Carlos Mallen "Krystal Peters" wrote: I want to use a macro to clean up a file and save time. The Requests Found field is the maximum # of records that can be deleted. So I could have 1 record with a max of 1 to delete or 5 of a record with a max of 3 to delete or 1 record with a max of 7 to delete. I assume I will need to loop through the rows - any suggestion would be appreaciated. Thanks, Krystal SAMPLE DATA: ACCT_NO Requests Found 289278995 1 289278999 1 289278999 1 289278999 1 870587008 1 119387014 1 158675527 2 654375649 2 569777245 2 752478468 2 752478468 2 396378512 2 396378512 2 396378512 2 396378512 2 399778090 3 399778090 3 399778090 3 208777882 4 208777882 4 208777882 4 208777882 4 987178737 4 987178737 4 117468837 7 117468837 7 117468837 7 117468837 7 |
How to delete correct # of rows
Krystal
The rationale for keeping/deleting decision is still not obvious. At first I thought you were probably deleting duplicates, but now that is clearly not the case as anything that is not duplicated is deleted. Beyond that, I don't see a pattern. Sometimes you only have one, but you can delete 2, so you delete it. In one case you have 5 can delete 4, so you keep only the last one. In another case you have 4 but you can delete 7 so you delete them all. In another case you have 3, can delete 1, so you delete only the last one. In another case you have 4, you can delete 2, so you keep only the last 2. I don't see how you can tell how many you can delete; and, then when given how many can be deleted, I don't see how you decide which ones to delete. Ken On Oct 12, 3:04*pm, Krystal Peters wrote: For (2) Col A does not increment as they are acct #'s; Col B let me know the maximum # of rows I can delete. *Below I showed in a third col what i would like to happen... SAMPLE DATA: ACCT_NO * *Requests Found * * * action to take 289278995 * * * * * *1 * * * * * * * * * * * * *delete 289278999 * * 1 * * * * * * * * * * * * *keep 289278999 * * 1 * * * * * * * * * * * * *keep 289278999 * * 1 * * * * * * * * * * * * *delete 870587008 * * * * * *1 * * * * * * * * * * * * *delete 119387014 * * * * * *1 * * * * * * * * * * * * *delete 158675527 * * 2 * * * * * * * * * * * * *delete 654375649 * * 2 * * * * * * * * * * * * *delete 569777245 * * 2 * * * * * * * * * * * * *delete 752478468 * * 2 * * * * * * * * * * * * *delete 752478468 * * 2 * * * * * * * * * * * * *delete 396378512 * * 2 * * * * * * * * * * * * *delete 396378512 * * 2 * * * * * * * * * * * * *delete 396378512 * * 2 * * * * * * * * * * * * *keep * * 396378512 * * 2 * * * * * * * * * * * * *keep 399778090 * * 3 * * * * * * * * * * * * *delete * 399778090 * * 3 * * * * * * * * * * * * *delete * 399778090 * * 3 * * * * * * * * * * * * *delete * 208777882 * * 4 * * * * * * * * * * * * *delete * 208777882 * * 4 * * * * * * * * * * * * *delete * 208777882 * * 4 * * * * * * * * * * * * *delete * 208777882 * * 4 * * * * * * * * * * * * delete * 208777882 * * 4 * * * * * * * * * * * * keep 987178737 * * 4 * * * * * * * * * * * * delete * 987178737 * * 4 * * * * * * * * * * * * *delete * 117468837 * * 7 * * * * * * * * * * * * delete * 117468837 * * 7 * * * * * * * * * * * * delete * 117468837 * * 7 * * * * * * * * * * * * delete * 117468837 * * 7 * * * * * * * * * * * *delete * * |
How to delete correct # of rows
Ok, by reading each of your reply posts, I think I understand your logic, so
I'll post that here for your confirmation and for the benefit of the other folks following this thread. For each account number in column A, count the number of times that item occurs in column A (CountIf). Then, look at the number in column B, which appears to always remain the same for any specific account number. Subtract this number from the CountIf from Column A. That represents the number of "deletes", and all other rows would be ignored. Krystal- Do you just want that final number per account, or do you need any additional information? What information do you want returned in situations where the number of "rows to delete" (column B) is greater than the number of times that account number shows up (CountIf on column A)? "Krystal Peters" wrote: For (2) Col A does not increment as they are acct #'s; Col B let me know the maximum # of rows I can delete. Below I showed in a third col what i would like to happen... SAMPLE DATA: ACCT_NO Requests Found action to take 289278995 1 delete 289278999 1 keep 289278999 1 keep 289278999 1 delete 870587008 1 delete 119387014 1 delete 158675527 2 delete 654375649 2 delete 569777245 2 delete 752478468 2 delete 752478468 2 delete 396378512 2 delete 396378512 2 delete 396378512 2 keep 396378512 2 keep 399778090 3 delete 399778090 3 delete 399778090 3 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 keep 987178737 4 delete 987178737 4 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete |
How to delete correct # of rows
This might help:
=IF(COUNTIF(A$1:A2,A2)=1,COUNTIF(A$1:A69,A2)-B2,"") Plug this formula into C2 and copy it down. Positive numbers gives the appropriate number of rows to "keep", and negative numbers indicates that there are more deletes than rows for that account. Based on the information returned, how does this relate to what you are actually looking for? I added the d/k column based on your original expected results, to see how they match up. Results: 289278995 1 0 d 289278999 1 2 k 289278999 1 k 289278999 1 d 870587008 1 0 d 119387014 1 0 d 158675527 2 -1 d 654375649 2 -1 d 569777245 2 -1 d 752478468 2 0 d 752478468 2 d 396378512 2 2 d 396378512 2 d 396378512 2 k 396378512 2 k 399778090 3 0 d 399778090 3 d 399778090 3 d 208777882 4 1 d 208777882 4 d 208777882 4 d 208777882 4 d 208777882 4 k 987178737 4 -2 d 987178737 4 d 117468837 7 -3 d 117468837 7 d 117468837 7 d 117468837 7 d "Krystal Peters" wrote: For (2) Col A does not increment as they are acct #'s; Col B let me know the maximum # of rows I can delete. Below I showed in a third col what i would like to happen... SAMPLE DATA: ACCT_NO Requests Found action to take 289278995 1 delete 289278999 1 keep 289278999 1 keep 289278999 1 delete 870587008 1 delete 119387014 1 delete 158675527 2 delete 654375649 2 delete 569777245 2 delete 752478468 2 delete 752478468 2 delete 396378512 2 delete 396378512 2 delete 396378512 2 keep 396378512 2 keep 399778090 3 delete 399778090 3 delete 399778090 3 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 keep 987178737 4 delete 987178737 4 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete |
How to delete correct # of rows
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Krystal Peters" wrote in message ... For (2) Col A does not increment as they are acct #'s; Col B let me know the maximum # of rows I can delete. Below I showed in a third col what i would like to happen... SAMPLE DATA: ACCT_NO Requests Found action to take 289278995 1 delete 289278999 1 keep 289278999 1 keep 289278999 1 delete 870587008 1 delete 119387014 1 delete 158675527 2 delete 654375649 2 delete 569777245 2 delete 752478468 2 delete 752478468 2 delete 396378512 2 delete 396378512 2 delete 396378512 2 keep 396378512 2 keep 399778090 3 delete 399778090 3 delete 399778090 3 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 delete 208777882 4 keep 987178737 4 delete 987178737 4 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete 117468837 7 delete |
| All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com