remove duplicates using vba
 

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


--
quigleyj

remove duplicates using vba
 

Try this formula.

Simon.

http://www.exceltip.com/st/Formula_i...range/821.html


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=379024

remove duplicates using vba
 

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


"Simon Lloyd" wrote:


Try this formula.

Simon.

http://www.exceltip.com/st/Formula_i...range/821.html


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=379024