remove duplicates using vba

[jeff quigley]

Hello
Sorry if you have seen this post already (I accidently posted it in Excel
Misc), but I wanted to make sure I posted it under the Excel Programming
Group.

I have been trying to Chip Pearsons code to remove duplicates using vba in
Excel. The code works perfectly when I create a macro in Excel and run it.
However I am trying to use this code within the ArcGIS visual basic editor.
I can open the spreadsheet and select a range using the code within ArcGIS
but when I try and incorporate the code for deleting duplicates it give me a
Compile Error: Wrong number of arguments or invalid property assignment. It
hightlights

V = Rng.Cells(r, 1).value as being the source of the error. Any
suggestions. Thanks in advance.



Attached code



Dim appExcel As New Excel.Application



' Set wbook = appExcel.Workbooks.Open("G:\Notification_App\jeff2.xls")

' Set wsheet = appExcel.Sheets("Sheet1")

appExcel.Workbooks.Open ("G:\Notification_App\jeff2.xls")

appExcel.Visible = False





Dim Col As Integer

Dim r As Long

Dim C As Range

Dim N As Long

Dim V As Variant

Dim Rng As Range



Range("F2:F100").Select



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

 

[Simon Lloyd]

Try this formula.

Simon.

http://www.exceltip.com/st/Formula_indicating_duplicate_values_in_a_range/821.html

 

[jeff quigley]

Thanks for tip Simon. Unfortuantely Im not sure this will do what I want it
to. The code I have works perfectly when ran inside Microsoft Excel, but I
contnue to get errors when I try and use the code in the outside program
(ArcGIS).

quigleyj