Ignorer cellules vides dans plage de cellules

[Nico44044]

Bonsoir à tous,

Je souhaiterais pouvoir effectuer une recherche dans une feuille excel en effectuant une comparaison entre plusieur plage de cellules, en ignorant les cellules vide.(le tout en VBA)

J'ai un début de piste mais je bloque pour la suite.

Je joint un fichier avec plus de precisions ...

Ce code fonctionne il ne me reste plus qu'a trouver comment ignorer les criteres vide dans Range("A1:C1")


Option Explicit Sub es()
Dim a, b, c, d As Variant, i As Long, j As Long, t As Long, J1 As Long
Application.ScreenUpdating = False
With ActiveSheet.UsedRange For t = 2 To 15
a = Range("A1:C1").Value
b = Range("A" & t & ":C" & t).Value
c = Range("D" & t & ":F" & t).Value
d = Range("G" & t & ":I" & t).Value
For i = LBound(a, 2) To UBound(b, 2)
For j = LBound(a, 1) To UBound(a, 1)
If a(j, i) = b(j, i) Or a(j, i) = c(j, i) Or a(j, i) = d(j, i) Then
Else
Rows(t).Hidden = True
End If
Next j
Next i
Next t
End With
End Sub

Merci d'avance à celui qui pourra m'aider

 

[job75]

Re,

Ma dernière solution est non seulement très rapide mais très facilement adaptable à plusieurs critères.

Il suffit d'ajouter des ",COUNTIF(A3:I3,<lettre>$1)+ISBLANK(<lettre>$1) dans la formule.

A+

 

[BOISGONTIER]

0,2 sec pour 1.000 lignes, c'est énorme!!!

Boisgontier

 

[Nico44044]

0,2 sec pour 1.000 lignes, c'est énorme!!!

Boisgontier

Effectivement c'est rapide et très simple à utiliser.

Je voudrais juste rajouter la chose suivante :
comment faire dans ton code pour que le filtre prennent meme des valeurs partielles ?

Par exemple pour trouver "Paris" je voudrais aussi qu'il prennent en compte "Par" ...

je pense que c'est avec "*" mais je ne trouve pas ou le placer dans ton code ...

 

[job75]

Re JB,

0,2 sec pour 1.000 lignes, c'est énorme!!!

J'avais testé le 1er fichier que tu avais déposé, il prenait plus de temps.

Cela dit chez moi sur 50 000 lignes le fichier de ton post #12 s'exécute en 6 secondes, test ci-joint.

A+

 

[BOISGONTIER]

Avec cette version , on peut frapper Fi* ou choisir dans un menu déroulant

Boisgontier

 

[Nico44044]

Le seul truc qui manque c'est de pouvoir taper un critère partiel sans rajouter le * à la saisie ...
En tapant uniquement Fia pour trouver Fiat par exemple.
C'est important car les critères seront alimenté dans le projet final par un UF donc pas de possibilité de rajouter le *.

Si vous me trouvez ca je suis au top ! 🙂

 

[BOISGONTIER]

cf PJ

Boisgontier

 

[Nico44044]

cf PJ

Boisgontier

Merci Boisgontier c'est nickel.

 

[job75]

Re,

Par exemple pour trouver "Paris" je voudrais aussi qu'il prennent en compte "Par" ...

Fichier (4) avec la formule utilisant SEARCH (CHERCHE) :

    .Cells(3, 10) = "=AND(SUMPRODUCT(-ISNUMBER(SEARCH(""*""&A$1,A3:I3))),SUMPRODUCT(-ISNUMBER(SEARCH(""*""&B$1,A3:I3)))" _
        & ",SUMPRODUCT(-ISNUMBER(SEARCH(""*""&C$1,A3:I3))))" 'critères

Notez que l'astérisque "*" évite d'utiliser ISBLANK (ESTVIDE).

A+

 

[Nico44044]

Re,

Fichier (4) avec la formule utilisant SEARCH (CHERCHE) :

    .Cells(3, 10) = "=AND(SUMPRODUCT(-ISNUMBER(SEARCH(""*""&A$1,A3:I3))),SUMPRODUCT(-ISNUMBER(SEARCH(""*""&B$1,A3:I3)))" _
        & ",SUMPRODUCT(-ISNUMBER(SEARCH(""*""&C$1,A3:I3))))" 'critères

Notez que l'astérisque "*" évite d'utiliser ISBLANK (ESTVIDE).

A+

Merci Bcp Job, j'ai appris plein de truc grace a ton code

 

[Nico44044]

cf PJ

Boisgontier

Encore besoin d'un dernier petit coup de main avec ton code Boisgontier :

Pour les besoin du projet final j'ai besoin de faire démarrer le tableau en F22 (comme sur le fichier joint)
J'ai fait des modifier mais cela ne donne rien quand je filtre ...

Merci de ton aide ;-)

 

[BOISGONTIER]

tblBD = Range("F23:Z" & [F65000].End(xlUp).Row).Value

Boisgontier

 

[Nico44044]

tblBD = Range("F23:Z" & [F65000].End(xlUp).Row).Value

Boisgontier

Mais oui suis-je bête !!!! j'avais tout regardé sauf ca ... Merciiiiiiiiii 🙂 🙂 🙂

 

[job75]

Re,

Toujours avec le filtre avancé :

Sub Filtrer()
Dim deb As Range, ncol%, fin As Range
Set deb = Feuil1.[F22] '1ère cellule, à adapter
ncol = 21 'à adapter
Set fin = deb.Resize(, ncol).EntireColumn.Find("*", , xlValues, , xlByRows, xlPrevious)
If fin Is Nothing Then Exit Sub 'sécurité
If fin.Row < deb.Row + 1 Then Exit Sub 'tableau vide
Application.ScreenUpdating = False
With deb.Resize(fin.Row - deb.Row + 1, ncol)
    .Rows(2).EntireRow.Insert
    .Cells(2, 1) = "c1": .Cells(2, 1).AutoFill .Cells(2, 1).Resize(, 20) 'titres provisoires
    .Cells(3, ncol + 1) = "=ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&F24)*SEARCH(""*""&G$22,CHAR(1)&G24)*SEARCH(""*""&H$22,CHAR(1)&H24)*SEARCH(""*""&I$22,CHAR(1)&I24))" _
        & "+ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&M24)*SEARCH(""*""&G$22,CHAR(1)&N24)*SEARCH(""*""&H$22,CHAR(1)&O24)*SEARCH(""*""&I$22,CHAR(1)&P24))" _
        & "+ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&T24)*SEARCH(""*""&G$22,CHAR(1)&U24)*SEARCH(""*""&H$22,CHAR(1)&V24)*SEARCH(""*""&I$22,CHAR(1)&W24))"
    .Offset(1).AdvancedFilter xlFilterInPlace, .Cells(2, ncol + 1).Resize(2) 'filtre avancé
    .Cells(3, ncol + 1) = ""
    .Rows(2).EntireRow.Delete
End With
End Sub

La formule est plus compliquée car :

- la recherche se fait dans les colonnes dédiées de chacun des 3 tableaux

- des caractères CAR(1) sont insérés pour le cas où les cellules des tableaux sont vides, je vous laisse chercher pourquoi.

Fichier joint, testé sur 60 000 lignes => 1,8 seconde.

A+

 

[Nico44044]

Re,

Toujours avec le filtre avancé :

Sub Filtrer()
Dim deb As Range, ncol%, fin As Range
Set deb = Feuil1.[F22] '1ère cellule, à adapter
ncol = 21 'à adapter
Set fin = deb.Resize(, ncol).EntireColumn.Find("*", , xlValues, , xlByRows, xlPrevious)
If fin Is Nothing Then Exit Sub 'sécurité
If fin.Row < deb.Row + 1 Then Exit Sub 'tableau vide
Application.ScreenUpdating = False
With deb.Resize(fin.Row - deb.Row + 1, ncol)
    .Rows(2).EntireRow.Insert
    .Cells(2, 1) = "c1": .Cells(2, 1).AutoFill .Cells(2, 1).Resize(, 20) 'titres provisoires
    .Cells(3, ncol + 1) = "=ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&F24)*SEARCH(""*""&G$22,CHAR(1)&G24)*SEARCH(""*""&H$22,CHAR(1)&H24)*SEARCH(""*""&I$22,CHAR(1)&I24))" _
        & "+ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&M24)*SEARCH(""*""&G$22,CHAR(1)&N24)*SEARCH(""*""&H$22,CHAR(1)&O24)*SEARCH(""*""&I$22,CHAR(1)&P24))" _
        & "+ISNUMBER(SEARCH(""*""&F$22,CHAR(1)&T24)*SEARCH(""*""&G$22,CHAR(1)&U24)*SEARCH(""*""&H$22,CHAR(1)&V24)*SEARCH(""*""&I$22,CHAR(1)&W24))"
    .Offset(1).AdvancedFilter xlFilterInPlace, .Cells(2, ncol + 1).Resize(2) 'filtre avancé
    .Cells(3, ncol + 1) = ""
    .Rows(2).EntireRow.Delete
End With
End Sub

La formule est plus compliquée car :

- la recherche se fait dans les colonnes dédiées de chacun des 3 tableaux

- des caractères CAR(1) sont insérés pour le cas où les cellules des tableaux sont vides, je vous laisse chercher pourquoi.

Fichier joint, testé sur 60 000 lignes => 1,8 seconde.

A+

Merci Job !
Je vais tester également ;-)