Isinarray pour supprimer lignes existantes d'une feuille à l'autre

[Preparation]

Bonjour,

Je souhaite utiliser la fonction isinarray pour mettre à jour ma base de données.
Sur le fichier exemple ci joint je souhaiterais supprimer les lignes du tableau en Feuil1 qui existes déjà dans le tableau de la Feuil2.
Je souhaiterais utiliser la fonction isinarray car mon fichier comporte plus de 10000 lignes mais j'ai un peu de mal à comprendre la mise en oeuvre de cette fonction.
Regarde la pièce jointe isinarray.xlsm


Merci d'avance.

 

[Misange]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour
Il n'y a pas de fonction native d'excel isinarray comme on le trouve dans d'autres langages de programmation.
Tu trouveras ici
Ce lien n'existe plus
une fonction perso mais elle est faite pour des arrays à une dimension, pas à deux. Tout array rempli à partir des données d'une feuille excel, même si ces données sont sur une seule colonne (ou ligne) est un array à 2 dimensions.
De multiples solutions pour traiter le problème des doublons ici
Ce lien n'existe plus

 

[job75]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour Preparation, salut Misange

Voyez le fichier joint avec ce code :

Option Compare Text 'la casse est ignorée

Sub SuppLignesExistantes()
Dim t1, t2, ub&, ncol%, rest(), i&, sup As Boolean, j&, k%, n&
t1 = Feuil1.[A1].CurrentRegion.Offset(1)
t2 = Feuil2.[A1].CurrentRegion.Offset(1)
If UBound(t1, 2) <> UBound(t2, 2) Then _
MsgBox "Les tableaux n'ont pas le même nombre de colonnes !": Exit Sub
ub = UBound(t2): ncol = UBound(t1, 2)
ReDim rest(1 To UBound(t1), 1 To ncol)
For i = 1 To UBound(t1)
  sup = False
  For j = 1 To ub
    For k = 1 To ncol
      If t1(i, k) <> t2(j, k) Then GoTo 1
    Next k
    sup = True
    Exit For
1 Next j
  If Not sup Then
    n = n + 1
    For k = 1 To ncol
      rest(n, k) = t1(i, k)
    Next k
  End If
Next i
Feuil1.[A1].CurrentRegion.Offset(1) = rest
End Sub

Edit : j'ai ajouté Option Compare Text 'la casse est ignorée

A+

 

[job75]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Re,

Pour tester un cas limite, j'ai exécuté la macro avec 2 tableaux 10000 x 5.

Les lignes sont identiques sauf la dernière colonne remplie par =ALEA().

La macro doit donc effectuer 2 500 000 000 comparaisons.

Sur Win 7 - Excel 2010 elle s'exécute en 3 minutes 43 secondes.

A+

 

[Preparation]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour Misange et Job75,

Merci pour vos réponses rapides.

Misange, j'ai consulté ces liens mais je n'arrive pas à les utiliser (dimensions de tableaux différentes et conséquentes, formules trop lourdes...)

Job75, c'est exactement ce que je cherchais à faire mais je ne suis pas assez spécialiste!! en tout cas merci pour ce code.

Juste 2 questions car j'ai mal formulé ma demande à la base:
* il faudrait que la comparaison ne se fasse que sur une seule colonne mais qui n'est pas la même entre les tableaux feuille 1 et feuille 2 (dans cet exemple feuille 1 colonne A et feuille 2 colonne C, je renvoi un nouveau petit fichier, la comparaison se ferai ici sur le code client, et les tableaux n'ont pas les même intitulés de colonne)
* pourriez-vous SVP mettre quelques commentaires sur le code pour que je puisse bien le comprendre?

En tout cas merci encore pour vos réponses et votre implication.


Cordialement.

 

[Misange]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour, salut Job

As tu vraiment besoin de faire cela par macro ? Une méthode beaucoup plus simple (et rapide !):
tu ajoutes une colonne dans la feuille 1 qui teste si le code client est présent dans la feuille 2. Un simple nb.si suffit à renvoyer 1 ou 0. Tu tries ensuite cette colonne et tu supprimes les lignes avec un 1.

mets tes tableaux sous forme de vrai tableau excel (onglet accueil/style/mettre sous forme de tableau. Ce n'est pas pour des raisons esthétiques (quoique) mais parce que cela t'apporte de nombreuses fonctionnalités. Voir ici pour plus de détails
Ce lien n'existe plus

 

[job75]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour Preparation, le forum,

S'il n'y a que 2 colonnes à comparer c'est évidemment bien plus rapide :

Sub SuppLignesExistantes()
Dim t1, t2, ub&, ncol%, rest(), i&, sup As Boolean, j&, n&, k%
t1 = Feuil1.[A1].CurrentRegion.Offset(1)
t2 = Feuil2.[A1].CurrentRegion.Offset(1)
If UBound(t2, 2) < 3 Then Exit Sub 'sécurité
ub = UBound(t2): ncol = UBound(t1, 2)
ReDim rest(1 To UBound(t1), 1 To ncol)
For i = 1 To UBound(t1)
  sup = False
  For j = 1 To ub
    If t1(i, 1) = t2(j, 3) Then sup = True: Exit For
  Next j
  If Not sup Then 'copie la ligne
    n = n + 1
    For k = 1 To ncol
      rest(n, k) = t1(i, k)
    Next k
  End If
Next i
With Feuil1.[A1].CurrentRegion.Offset(1)
  .Cells = rest 'copie le tableau
  .Rows(n + 1 & ":" & .Rows.Count).Delete xlUp 'supprime les bordures
End With
End Sub

Fichier (2).

A+

 

[Preparation]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour Misange,

Cette solution me parait très bien mais je n'ai pas réussi à la mettre en oeuvre, cela fait 3 heures que je me tire les cheveux!!
Je vais mettre mon fichier original allégé pour que vous comprenniez ou ça bloque.. mais la formule proposée ne se calcul pas (colonne AC), pourtant je suis bien en mode de calcul automatique.

Merci pour votre aide.
Cordialement.

 

[job75]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Re,

Sur votre nouveau fichier les colonnes à comparer sont B (1ère feuille) et C (2ème feuille).

Voyez les 2 fichiers joints.

Le 1er reprend ma macro du post #7.

Le 2ème utilise la fonction NB.SI comme suggéré par Misange :

Sub SuppLignesExistantes()
Dim a$, P As Range
Application.ScreenUpdating = False
a = Feuil2.[A1].CurrentRegion.Columns(3).Address(, , xlR1C1, True)
With Feuil1.[A1].CurrentRegion.Offset(1)
  .Columns(1).EntireColumn.Insert
  Set P = .Columns(0)
  P.FormulaR1C1 = "=LN(COUNTIF(" & a & ",RC3))"
  P = P.Value 'ne garde que les valeurs
  On Error Resume Next 's'il n'y a rien à supprimer
  Intersect(P.SpecialCells(xlCellTypeConstants, 1).EntireRow, .Cells).Delete xlUp
  P.EntireColumn.Delete
End With
End Sub

A+

 

[Preparation]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Re,

Merci à vous 2, Job75 et Misange, pour votre aide.
J'ai bien réussi à insérer le code de Job75 dans ma macro, le temps de traitement est un peu long vu la taille de mon tableau mais c'est nickel!!

C'est ma 1ère demande sur ce forum et je suis très satisfait!!

SUPER!

Merci tout plein
@+

 

[Misange]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

C'est tout simplement parce que le format de tes cellules dans la colonne verif est en format texte que tes formules se ne calculent pas il faut les mettre en format standard ou nombre.
il faut supprimer cette colonne du tableau
ajouter une nouvelle colonne,
la sélectionner et la mettre en format standard
et seulement ensuite ajouter ta formule
En effet si ta colonne est formatée en texte AVANT d'écrire ta formule, tu ne peux plus exécuter les formules que tu y inscris.


Tu peux faire cette manip par macro comme proposé par Job mais manuellement si ton tableau est long ce sera plus rapide.

 

[Preparation]

Re : Isinarray pour supprimer lignes existantes d'une feuille à l'autre

Bonjour Job75 et Misange,

Juste pour info, à mon retour de congé j'ai re-planché sur mon tableau:

J'ai appliqué la méthode de Misange : mise sous forme de tableau et traitement avec filtres et formules (avec INDEX et EQUIV) et là le temps de traitement est très très court!! (grâce à l'aide de excelabo)
Par contre mon code n'est vraiment pas aussi allégé que celui de Job75 dans son message du 31/07 à 11h! Le jour où j'arriverai à faire ça... CHAMPAGNE!!!

De plus,cette méthode est plus simple à comprendre donc à traiter et modifier.

Je ne suis pas encore assez calé sous VBA Excel pour faire des macros comme celle proposée par Job dans son premier post (déclaration de tableaux UBound / Redim...) mais je suis sûr que les utilisations peuvent être variées et intéressantes. (J'avoue que je n'arrive pas à assimiler complètement la méthode et la logique)

En tout cas merci beaucoup pour votre aide rapide et efficace.