Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove all duplicates? | Excel Discussion (Misc queries) | |||
Remove duplicates | Excel Worksheet Functions | |||
remove duplicates | Excel Discussion (Misc queries) | |||
Remove Duplicates | New Users to Excel | |||
Remove duplicates | Excel Discussion (Misc queries) |