Excel macro works on PC, not on Mac

September 1, 2011 at 13:10:32
Specs: Windows 7
The following is Chip pearsons well-know Delete Duplicate Rows macro. Have used it for a long time on Excel 2003, 2007 and 2010. Works with CSV files or XLS files. We load up a blank sheet with macro, open that, then minimize that file.

For using with all other work files select Across all Open Workbooks on the working files

When trying this same macro with same method with Macintosh Excel 2011, the macro runs, but does not delete the obviously duplicate rows. we are using same text CSV files and XLS files on the Mac. It will run but always reports "Deleted 0 Rows" and there are duplicate rows

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, _

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
        N = N + 1
    End If
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        N = N + 1
    End If
End If
Next R


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

End Sub

See More: Excel macro works on PC, not on Mac

Report •

September 1, 2011 at 15:49:58
I don't have a Mac available to test this on but I'll suggest this:

Assuming the steps outlined in this How To work on a Mac, perhaps you can find the trouble by Single Stepping through the code and watching what is going on:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 1, 2011 at 16:14:24
Thanks. That's good advice and the long way home,-. :) Thanks for the link

If anyone else has seen this issue and resolved it, please weigh in and let me know

Report •

Related Solutions

Ask Question