How Can You Fix PC Dll Errors Effectively?: Troubleshoot:Macro to delete duplicate records Error

2016年2月21日星期日

Troubleshoot:Macro to delete duplicate records Error

Macro to delete duplicate records

I would love to have a macro that deletes duplicate records.
I would like said macro to prompt me which columns and rows to search for duplicate values and then any duplicate rows based on those inputs.
For example, in rows 1-100, if I want every instance wherein column A has a duplicate, delete all the rows with 1-100 with duplicate values.
Another example, in rows 1-100, if I want every instance wherein column A and column B both has a duplicate value,
delete all those rows with a duplicate value.
Am I making sense?
I very much appreciate your help.

Anwsers to the Problem Macro to delete duplicate records

Download SmartPCFixer for Free Now

This from www.cpearson.com

DeleteDuplicateRows
This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g.,C2:C99.   To determine whether a row has duplicates, the values in theselected
column are compared.
Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in theactive column, the first row remains, and all subsequent rows are
deleted.
Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column.
That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

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

Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub

hth
Vaya con Dios,
Chuck, CABGx3

Windows Error Reporting - To Fix Macro to delete duplicate records

Go to Start button.
Open Control Panel.
Go to Windows Error Reporting.
Click on System and Maintenance, then click Problem Reports and Solutions.
Next, click Change Settings located on the left side panel of the Problem Reports and Solutions window.
Select an option to configure how you want Windows to look for a solution to your problems. You may allow Windows to do it automatically or ask you every time to check if problem occurs.
Next, click the Advanced settings link.
Select Off to turn off Error Reporting.