Your code expects expects you (the user) to have the range to clean up already
selected.

Do you select the range first, then invoke the macro?

wrote:

I am having a problem with my macros not wanting to run if I use
"selection" in the coding. I am using the exact same code that I had
been using in the past, i just placed it in a new add-in I am creating.
But now I keep getting errors. Is there such a thing as a macro that
will screw up "selection" in the whole workbook? Sometimes I can place
individual macros in workbooks and it will work.

here is an example of code that I use:

Sub MarkDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String

Application.ScreenUpdating = False
On Error Resume Next
Set rAllRange = Selection
If WorksheetFunction.CountA(rAllRange) < 2 Then
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Selection.EntireColumn.Insert

Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)

If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
Set rAllRange = Union(rConstRange, rFormRange)
ElseIf Not rConstRange Is Nothing Then
Set rAllRange = rConstRange
ElseIf Not rFormRange Is Nothing Then
Set rAllRange = rFormRange
Else
MsgBox "Your selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Application.Calculation = xlCalculationManual

For Each rCell In rAllRange
strAdd = rCell.Address
strAdd = rAllRange.Find(What:=rCell, After:=rCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Address

If strAdd < rCell.Address Then
rCell.Offset(0, -1).Value = "Duplicate"
End If
Next rCell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub

In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

--

Dave Peterson