G
Guest
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
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