View original (French)

VBA Excel: Remove duplicates with 2 criteria

Clairette -  
 Clairette -
Hello,

I'm looking to remove duplicate rows in my list based on two criteria. But I'm really stuck. I'm completely lost here... if someone could help me please!!!

I had gotten to this point:

x.Range("B2:C2").Select
donnee1 = x.Range("B2:C2")
ActiveCell.Offset(1, 0).Select
While ActiveCell <> ""
If ActiveCell = donnee1 Then
ActiveCells.EntireRow.Delete
ActiveCells.Offset(-1, 0).Select
donnee1 = ActiveCell
ActiveCells.Offset(1, 0).Select
Else
donnee1 = ActiveCells
ActiveCells.Offset(1, 0).Select
End If
Wend

I thank those who will at least take a look!!!

Configuration: Windows XP / Internet Explorer 6.0

4 answers

  1. Clairette
     
    No one has the slightest idea??? Another technique???
    0
  3. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
     
    Hello,

    in the Excel 2007/2010 version, you can go to the Data tab, "Data Tools", "Remove Duplicates" tool. For other versions, it should be possible but I don't know how to access it.

    Best regards.
    --
    This guy was not very bright, as he was foolish.
    0
    1. Clairette
       
      Thank you chossette9, but I have Excel 2003... and on top of that, I would like to automate it upon closing the file. Otherwise, my file becomes a bit useless, so I can't explain to you how depressed I am... Excel will kill me!!!
      0
  4. Clairette
     
    If only someone could explain to me why this code:

    Dim y As Worksheet
    Set y = Workbooks("Fichier Commun.xls").Sheets("Factures mobilisées")
    Sheets("Factures mobilisées").Activate

    y.Range("A1:Z10000").Sort Key1:=Range("P1"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    works very well if I put it in a command button on the sheet but does not work at all if I put it in a command button on another sheet or in a beforeclose procedure or in a button placed in another file?

    The Sort method of the Range class fails... that is what prevents me from continuing... does anyone have an answer???
    0