Ignorer cellules vides dans plage de cellules

Nico44044 · 12 Octobre 2018

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 · 13 Octobre 2018

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 · 13 Octobre 2018

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

Boisgontier

Nico44044 · 13 Octobre 2018

BOISGONTIER à dit:
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 · 13 Octobre 2018

Re JB,

BOISGONTIER à dit:
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 · 13 Octobre 2018

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

Boisgontier

Nico44044 · 13 Octobre 2018

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 · 13 Octobre 2018

cf PJ

Boisgontier

Nico44044 · 13 Octobre 2018

BOISGONTIER à dit:
cf PJ

Boisgontier

Merci Boisgontier c'est nickel.

job75 · 13 Octobre 2018

Re,

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

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

Code:

    .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 · 13 Octobre 2018

job75 à dit:
Re,

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

Code:

.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 · 13 Octobre 2018

BOISGONTIER à dit:
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 · 13 Octobre 2018

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

Boisgontier

Nico44044 · 13 Octobre 2018

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

Boisgontier

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

job75 · 13 Octobre 2018

Re,

Toujours avec le filtre avancé :

Code:

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 · 13 Octobre 2018

job75 à dit:

Re,

Toujours avec le filtre avancé :

Code:

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 ;-)

Ignorer cellules vides dans plage de cellules

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

Discussions similaires