Miss - match error ?

[shumba]

Hi,


Column A has a list of three letter codes placed in each cell and is periodically updated.


The Sub Procedure below is designed to check the most recently added code against the codes already on the list. If the code is the same as any added before then it is deleted from the list. When the Sub Procedure is executed a Mismatch Error occurs at the part of the underlined statement.


Please will someone show me where I am going wrong .

Sub Check_for_previous_occurance_of_code()
      Sheets("User Interface").Select
      Range("a9").End(xlDown).Select
      [U]If Selection.Value = Range(Selection.Offset(-1, 0), Selection.End(xlUp)).Value[/U] Then Selection.ClearContents
  End Sub

Thanks,

Rob

 

[Michael M]

Hi Rob
It looks like you are selecting the entire column of data and then asking the code to use part of the entire selection to check itself....kinda dosen't make sense

Try

Sub delem()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr - 1 To 9 Step -1
        If Range("A" & lr).Value = Range("A" & r).Value Then Rows(lr).Delete
    Next r
End Sub

You could change this to a Worksheet_selectionchange event so that it checks as soon as the data is input.

Another tip, try and avoid the use of Select.Selection in your code. simply remove them.
So,

Sheets("User Interface").Select
      Range("a9").End(xlDown).Select
      If Selection.Value

would be

Sheets("User Interface").Range("a9").End(xlDown).Value........

 

[shumba]

Thanks Michael.

Regards,

Rob.