deleting identical rows
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: deleting identical rows

  1. #1
    Guest

    Default

     
    hi,

    Is it possible to delete identical rows in a sheet. (each row has about 10 cells of information: if all cells are identical I want to delete the row) I want to build a macro which does this everytime a user closes the sheet. (so on workbook_beforeclose)

    Hope anyway can help me on the way.

    Thnks!

    Remi
    Holland.

  2. #2

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you want to delete a row if it is the same as another row?
    Or do you want to delete a row if all the data in each cell of that row is the same?

  3. #3
    Guest

    Default

    The second one you suggest.

    Would you know a solution?
    thnks!

  4. #4

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to clarify what you want:- If the contents in each of the cells in A3:J3(for example) are the same, row 3 should be deleted.

    Try the following. It is assumed that the data in all rows start in column A

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sh As Worksheet
    Dim rng1 As Range, rng2 As Range, cell As Range
    Dim cCount%, Lc%, x%, toDelete As Range
    Set sh = Worksheets("Sheet1") 'Change sheet name as necessary
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set rng1 = Intersect(sh.UsedRange, Columns(1))
    For Each cell In rng1
    Lc = Cells(cell.Row, 256).End(xlToLeft).Column
    Set rng2 = Range(Cells(cell.Row, 1), Cells(cell.Row, Lc))
    cCount = rng2.Cells.Count
    If Application.WorksheetFunction.CountIf(rng2, cell) = cCount Then
    If x = 1 Then
    Set toDelete = Union(toDelete, cell)
    Else
    Set toDelete = cell
    x = 1
    End If
    End If
    Next
    toDelete.EntireRow.Delete
    ActiveWorkbook.Save
    Application.EnableEvents = True
    End Sub

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There's no need to do a macro here. You can use Excel's built in features that work much better !

    Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 05:45, Juan Pablo G. wrote:
    There's no need to do a macro here. You can use Excel's built in features that work much better !

    Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location.
    But that's not what the poster wants to do.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Then record a macro to do the ADvanced Filter... I bet it's just "a little" faster.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    Guest

    Default

    On 2002-02-25 01:16, Autolycus wrote:
    Do you want to delete a row if it is the same as another row?
    Or do you want to delete a row if all the data in each cell of that row is the same?
    Can you give me a macro for the first problem?(delete a row if it is the same as another rouw?Many thanks

  9. #9

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 07:07, Juan Pablo G. wrote:
    Then record a macro to do the ADvanced Filter... I bet it's just "a little" faster.

    A little faster than what? The macro I posted does not do the same thing as retaining only unique records.
    I asked the poster(or at least thought I had asked) if he wanted to retain unique records and he replied no.
    Obviously my question was not phrased very well because he is now asking for a macro to do that.



    [ This Message was edited by: Autolycus on 2002-02-27 14:44 ]

  10. #10

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-27 07:07, Anonymous wrote:
    On 2002-02-25 01:16, Autolycus wrote:
    Do you want to delete a row if it is the same as another row?
    Or do you want to delete a row if all the data in each cell of that row is the same?
    Can you give me a macro for the first problem?(delete a row if it is the same as another rouw?Many thanks
    Record a macro as suggested by Juan Pablo G :-
    "Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location."


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
  •  

 

 
DMCA.com