How to delete correct # of rows

[Krystal Peters]

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

 

[Carlos Mallen]

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

 

[Krystal Peters]

Thanks for the looping code. Any idea on how I can delete the correct number
of rows/records....?

 

[ker_01]

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

 

[Ken]

How do you know what the correct number to delete is? Are you just
trying to eliminate any duplications?

 

[Carlos Mallen]

Sure, you just say wks.Rows(RowNumber).Delete. That's it. I still don't get
what does the "correct number" of rows means.

 

[Don Guillett]

You need to give more info with after examples.

 

[Krystal Peters]

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

 

[JLGWhiz]

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]

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???

 

[Ken]

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

 

[ker_01]

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)?

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

 

[ker_01]

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

 

[Don Guillett]

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.