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

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
>

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

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 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
<KrystalPet...@discussions.microsoft.com> 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 -

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

You need to give more info with after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Krystal Peters" <(E-Mail Removed)> wrote in message
newsB24F88B-4DA8-401E-9012-(E-Mail Removed)...
>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
>

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

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" <(E-Mail Removed)> wrote in message
news:B04C9E00-8BEB-474A-9D18-(E-Mail Removed)...
> 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
>> >

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