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
"jimmy" wrote in message
...
I have a script that will delete duplicate entries for a
user selected column. My problem is that the data set i
am currently working on is HUGE, spread across 5 sheets,
and duplicates can occur on any of the 5 sheets. My
question is how to modify my script so that it checks
each
sheet instead of only one.
if thats too specific, i guess i could use the vb code
that would scan across multiple worksheets instead of
just
one. the file contains 340,000 rows.
.
..