remove duplicates using vba

J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top