Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates across sheets p. 2
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. . .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates across sheets p. 2
Believe this was answered in the original thread.
-- Regards, Tom Ogilvy jimmy wrote in message ... 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Duplicates | Excel Worksheet Functions | |||
Deleting Duplicates | Excel Discussion (Misc queries) | |||
Deleting Duplicates | Excel Discussion (Misc queries) | |||
Deleting duplicates | Excel Discussion (Misc queries) | |||
Deleting duplicates across sheets | Excel Programming |