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.

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.

The files dont contain anymore than 6 columns of
information, only the first 3 are used most. but 340,000
rows are used to keep track of file numbers. my code
looks like this....

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
Dim x As Object

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
Sheets(Array("sheet1", "sheet2")).Select
N = 0
For Each x In ActiveWindow.SelectedSheets
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
Next x


EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Thank you so much for your help
-----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.


.

Jimmy,

you have a good start there. I did a little extra and it seems to work. Post
back if it doesn't and maybe someone else can continue. It's kinda late in
Europe so I need to go to bed now...

HTH,
Jouni
Finland

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

Dim ws As Worksheet, ws2 As Worksheet
Dim intCounter As Integer

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

Col = ActiveCell.Column

N = 0

For Each ws In ActiveWorkbook.Sheets
ws.Activate
If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If


For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value

intCounter = 0

For Each ws2 In ActiveWorkbook.Sheets

intCounter = intCounter +
Application.WorksheetFunction.CountIf(ws2.Columns( 1), V)

Next ws2

If intCounter 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If

Next r


Next ws

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"jimmy" wrote in message
...
IM SORRY, but the code is actually this. the other was an
attempt at going across sheets. sorry and thanks a bunch.

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.


.

Hi Jimmy,

I don't know why you had the On Error GoTo EndMacro statement. I left it
there because you had it. Maybe something bizarre occurs and that's why it
exits too early. I don't know. I've tested the macro with one and three
sheets with different data in each test and I don't see any problems.

Please try removing the On Error statement. And _always_ remember that it's
a little dangerous to use On Error 's. If you have to use it, then you
should _always_ restore it by On Error Goto 0 statement after you don't need
it anymore. For example:

' some code here
on error resume next
cells.find(What:="Jimmy").activate
if err < 0 then
' not found.
else
' found
end if
on error goto 0 ' error trapping not needed anymore
' macro continues...

If you don't restore it, then you might miss an important error in code
later on.

If you receive an error message after you remove the on error statement,
post back telling what it says.

HTH,
Jouni
Finland

"jimmy" wrote in message
...
for some reason the inner for loop in the code is breaking
out of the inner and outer for loop before executing the
if statement containing the intCounter. Any
ideas...thanks again for your help, hope you slept well!!!
-----Original Message-----

Jimmy,

you have a good start there. I did a little extra and it
seems to work. Post
back if it doesn't and maybe someone else can continue.
It's kinda late in
Europe so I need to go to bed now...

HTH,
Jouni
Finland

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

Dim ws As Worksheet, ws2 As Worksheet
Dim intCounter As Integer

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

Col = ActiveCell.Column

N = 0

For Each ws In ActiveWorkbook.Sheets
ws.Activate
If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If


For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value

intCounter = 0

For Each ws2 In ActiveWorkbook.Sheets

intCounter = intCounter +
Application.WorksheetFunction.CountIf(ws2.Columns (1), V)

Next ws2

If intCounter 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If

Next r


Next ws

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"jimmy" wrote in message
...
IM SORRY, but the code is actually this. the other was
an
attempt at going across sheets. sorry and thanks a
bunch.

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.


.



.