Astuces Excel
  1. Astuces Excel
  3. La méthode Excel VBA – RemoveDuplicates ne fonctionne pas avec Mac
Intereting Posts
Conversion de données dans le file Excel en xml à l'aide de MS Excel 2007 Résumer datatables en fonction de la date Erreur de compilation Excel: Impossible d'affecter à la propriété en lecture seule Excel: Texte de la colonne Union dans Excel JavaScript: "Uncaught SecurityError" lors de la démo JS-XSL localement screenupdating = false, Excel UI va se figer Comment exporter datatables UITableView pour excel rapidement Comment donner le directory current en tant que nom de path pendant la lecture d'un file Excel? Comment puis-je éviter que des tables ne soient effectuées lors du sorting des données source? Comment écrire les fiches de command VB ("feuille1"). Sélectionnez dans "TCL" vsto: c # changement de localization des objects Excel UI, p.ex. ruban, au moment de l'exécution Comment copyr des données d'une feuille vers une autre feuille en utilisant Excel VBA Validation du type d'argument dans l'Assistant Fonction Vérifiez deux strings ont les mêmes valeurs mois et année VBA définit la valeur de la cellule dans un text contenant 'ne fonctionne pas

La méthode Excel VBA – RemoveDuplicates ne fonctionne pas avec Mac

J'ai un code ci-dessous pour supprimer les duplicates d'une feuille en regardant deux colonnes (colonne 3 et 5). 

lRow = .Cells(Rows.Count, "A").End(xlUp).Row '.Range("A1:BR" & lRow).RemoveDuplicates Columns:=Array(3, 5), Header:=xlYes .Range("$A$1:$BR$" & lRow).RemoveDuplicates Columns:=Array(3, 5), Header:=xlYes

Cela fonctionne bien dans Windows, mais malheureusement pas sur Mac.

Quelqu'un peut-il me suggérer ce que j'ai besoin de changer ici?

Ce code crée une list de valeurs uniques et copyr dans une autre cellule. Créez donc une list unique.

Vous devez spécifier l'endroit où votre list commence et où vous souhaitez copyr. Vous pouvez le faire en modifiant les variables fromCell et toCell. J'espère que ça aide.

Sub uniqueList () 

 fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue fromCell = "A1" fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue toCell = "B1" fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue fromColumn = Mid (fromCell, 1, 1) 'Cela résoudra A fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue toColumn = Mid (toCell, 1, 1) 'Cela résoudra à B fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue fromRow = Mid (fromCell, 2) 'Cela résoudra à 1 fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue toRow = Mid (toCell, 2) 'Cela résoudra à 1 fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Dim cl As Range, UniqueValues ​​As New Collection, uValue As Variant fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Application.Volatile fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue numRows = Range (fromCell) .End (xlDown) .Row fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Sur le résumé d'erreur Suivant fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Pour chaque cl en scope (fromCell & ":" & fromColumn & numRows) fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue UniqueValues.Add cl.Value, CStr (cl.Value) fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Suivant cl fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue y = toRow - 1 fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Pour chaque uValue dans UniqueValues fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue y = y + 1 fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue Range (toColumn & y) = uValue fromCell = "A1" toCell = "B1" fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A toColumn = Mid(toCell, 1, 1) 'This will resolve to B fromRow = Mid(fromCell, 2) 'This will resolve to 1 toRow = Mid(toCell, 2) 'This will resolve to 1 Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile numRows = Range(fromCell).End(xlDown).Row On Error Resume Next For Each cl In Range(fromCell & ":" & fromColumn & numRows) UniqueValues.Add cl.Value, CStr(cl.Value) Next cl y = toRow - 1 For Each uValue In UniqueValues y = y + 1 Range(toColumn & y) = uValue Next uValue

End Sub