Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Delete double name

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Excel lovers,

    I have a column (column A) with names.
    When I add a name to the column, I play a macro that deletes the name if the name is already in the column!

    But my solution doesn’t work.
    When I play the macro it always deletes the last name!

    Why?!
    I think it goes wrong with the end(xldown) part.
    When I play the macro step by step (with F8) the value of end(xldown) = 4121?!?!?!

    Sub Test()
    i = 1
    Do Until Cells(i, 1).Value = ""
    If Cells(1, 1).End(xlDown).Value = Cells(i, 1).Value Then
    MsgBox "This name already exists!"
    Cells(1, 1).End(xlDown).EntireRow.Delete
    End If
    i = i + 1
    Loop
    End Sub

    Who can help me?

    Best regards,

    Martin J.A. Maatman Oonk

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'm assuming that you are entering the new name on the next available row?
    The value is finding it'self as a match and then deleting it'self.
    Try this...


    Sub Test()
    Dim c, LastRow
    With ActiveSheet
    LastRow = .UsedRange.Columns(1).Rows.Count
    For Each c In .Range("a2:A" & LastRow)
    If .Cells(LastRow, 1) = c.Value Then
    If c.Row = LastRow Then Exit Sub
    MsgBox "This name already exists!"
    .Rows(LastRow).Delete
    End If
    Next
    End With
    End Sub


    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •