Have client request worksheet that has duplicates that must be removed so
clients are not overcharged. The second column (below) indicates the number
of
request received & max # of rows to delete. I have run into a problem when
the max # of rows to delete is more than the records available.

Sample:

ACCT_NO Requests Found
289278995 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

Code used:
sr = currentrow
For counter = 1 To countrows
delrow = ActiveSheet.Cells(sr, 7).Value 'On Paste_Accounts
acct1 = ActiveSheet.Cells(sr, 5).Value 'Acct # 1
acct2 = ActiveSheet.Cells(sr + 1, 5).Value 'Acct #2

If acct1 < acct2 Then
If delrow = 1 Then
ActiveSheet.Cells(sr, 1).Select
Selection.EntireRow.Delete
End If

If delrow 1 Then
r1 = (sr + 1) - delrow
r2 = sr
accts = ActiveSheet.Cells(r1, 5).Value
If accts = acct1 Then
Rows(r1 & ":" & r2).Select
Selection.EntireRow.Delete
sr = r1
Else
Rows(r2).Select
Selection.EntireRow.Delete
End If

End If
Else
sr = sr + 1
End If
Next counter
End Sub

Any help / suggestions would be appreciated.

Is that only COL A you want to ..Then try the below macro

-With data in Column A (cell A1 should have a header)..run this macro which
will insert a sheet after the current sheet and generate a list of unique
account numbers. Try and feedback

Sub Macro()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add(After:=ActiveSheet)
ws1.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Krystal Peters" wrote:

Have client request worksheet that has duplicates that must be removed so
clients are not overcharged. The second column (below) indicates the number
of
request received & max # of rows to delete. I have run into a problem when
the max # of rows to delete is more than the records available.

Sample:

ACCT_NO Requests Found
289278995 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

Code used:
sr = currentrow
For counter = 1 To countrows
delrow = ActiveSheet.Cells(sr, 7).Value 'On Paste_Accounts
acct1 = ActiveSheet.Cells(sr, 5).Value 'Acct # 1
acct2 = ActiveSheet.Cells(sr + 1, 5).Value 'Acct #2

If acct1 < acct2 Then
If delrow = 1 Then
ActiveSheet.Cells(sr, 1).Select
Selection.EntireRow.Delete
End If

If delrow 1 Then
r1 = (sr + 1) - delrow
r2 = sr
accts = ActiveSheet.Cells(r1, 5).Value
If accts = acct1 Then
Rows(r1 & ":" & r2).Select
Selection.EntireRow.Delete
sr = r1
Else
Rows(r2).Select
Selection.EntireRow.Delete
End If

End If
Else
sr = sr + 1
End If
Next counter
End Sub

Any help / suggestions would be appreciated.

My apologies, I was not clear.

If for an acct there is only 1 request found and only 1 record then it's to
be deleted but if only 1 request is found but there is 2 or more rows with
the acct # then only delete 1 rows. But if 4 requests are found for an
account and there is only 1 record then that record should be deleted. The
max number to be delete is based on the requests found. Does that help?


"Jacob Skaria" wrote:

Is that only COL A you want to ..Then try the below macro

-With data in Column A (cell A1 should have a header)..run this macro which
will insert a sheet after the current sheet and generate a list of unique
account numbers. Try and feedback

Sub Macro()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add(After:=ActiveSheet)
ws1.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Krystal Peters" wrote:

Have client request worksheet that has duplicates that must be removed so
clients are not overcharged. The second column (below) indicates the number
of
request received & max # of rows to delete. I have run into a problem when
the max # of rows to delete is more than the records available.

Sample:

ACCT_NO Requests Found
289278995 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

Code used:
sr = currentrow
For counter = 1 To countrows
delrow = ActiveSheet.Cells(sr, 7).Value 'On Paste_Accounts
acct1 = ActiveSheet.Cells(sr, 5).Value 'Acct # 1
acct2 = ActiveSheet.Cells(sr + 1, 5).Value 'Acct #2

If acct1 < acct2 Then
If delrow = 1 Then
ActiveSheet.Cells(sr, 1).Select
Selection.EntireRow.Delete
End If

If delrow 1 Then
r1 = (sr + 1) - delrow
r2 = sr
accts = ActiveSheet.Cells(r1, 5).Value
If accts = acct1 Then
Rows(r1 & ":" & r2).Select
Selection.EntireRow.Delete
sr = r1
Else
Rows(r2).Select
Selection.EntireRow.Delete
End If

End If
Else
sr = sr + 1
End If
Next counter
End Sub

Any help / suggestions would be appreciated.