Deleting duplicates across sheets p. 2

[jimmy]

Jouni, The file actually only contains 6 columns of
information, the rest contain file numbers that need to be
tracked. I dont have 255*65536 cells of information, i
have 340,000 rows of information. This is the code that I
am using to delete duplicates in one sheet. Thanks again
for your help.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection.
Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns
(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

-----Original Message-----
Hi Jimmy,

one worksheet holds 255*65,536 cells, that is 16,711,680 cells, much more
than your 340,000 rows. Can you spread your data to several columns?

I don't know how your script works, but you can always go through worksheets
in a workbook by a script:

Sub Example()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
' your code here...
Next ws

End Sub

Or maybe "your code" is wrapped around the For Next loop. Depends on how
your code is written.

HTH,
Jouni
Finland




.

..

 

[Tom Ogilvy]

Believe this was answered in the original thread.