Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Something wrong with this code.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the following piece of code, which I was hoping would delete any lines when matched with any other string in Column 1.

    Sub Del_Entries()

    Dim Store_Ins As String

    Set FixWS = Workbooks("test.xls").Worksheets("ion")

    rr = 1

    Store_Ins = Cells(rr, 1).Value

    rr = rr + 1

    While (FixWS.Cells(rr, 1).Value <> "")
    If (FixWS.Cells(rr, 1).Value = Store_Ins) Then
    FixWS.Cells(rr, 1).EntireRow.Delete Shift:=xlShiftUp
    Else
    Store_Ins = Cells(rr, 1).Value
    End If
    rr = rr + 1
    Wend

    End Sub



    So, here's some data to work with:

    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    FFF/MMM 07/05/02
    FFF/MMM 07/05/02
    FFF/MMM 07/05/02
    FFF/MMM 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    RRR/OOO 07/05/02
    XXX/WWW 07/05/02
    XXX/WWW 07/05/02
    XXX/WWW 07/05/02
    XXX/WWW 07/05/02
    XXX/WWW 07/05/02
    XXX/WWW 07/05/02



    This should only leave RRR/OOO, FFF/MMM, XXX/WWW and their relative date.

    For some reason it doesn't perform the task in one go, I have to run it a few times. Can you help?

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chip Pearson's code at this address produces the desired result when run on your sample data.

    http://www.cpearson.com/excel/deleti...eDuplicateRows

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've "fixed" your code, sort of, but there is an error in your loop. This loop only compares for identical values that are in continuous rows, so the result is:

    RRR/OOO 07/05/02
    FFF/MMM 07/05/02
    RRR/OOO 07/05/02
    XXX/WWW 07/05/02


    Here is the modified code:


    Sub Del_Entries()

    Dim Store_Ins As String

    Set FixWS = Workbooks("test.xls").Worksheets("ion")

    rr = 1

    Store_Ins = Cells(rr, 1).Value

    rr = rr + 1

    While (FixWS.Cells(rr, 1).Value <> "")
    If (FixWS.Cells(rr, 1).Value = Store_Ins) Then
    FixWS.Cells(rr, 1).EntireRow.Delete Shift:=xlShiftUp
    Else
    rr = rr + 1
    Store_Ins = Cells(rr, 1).Value
    End If
    Wend

    End Sub


    You can mess about with it to get it to do what you want, or if you want a suggestion, just repost.

    HTH

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
  •