Discussion :

[kev59600]

Bonjour,

dans le cadre d'un projet professionnel, je réalise une macro qui vérifie les écarts entre deux fichiers (A et B).
Chaque ligne contient un identifiant unique qui est la combinaison de 2 colonnes.

En résumé, rien de bien sorcier :
- J'applique un filtre.
- Pour chaque ligne du fichier A, je compare les 2 colonnes (qui servent d'identifiant) avec 2 autres colonnes (aussi identifiant) du fichier B, pour chaque ligne aussi. Je procèdes avec des FOR EACH ligne IN plageA.Rows
- Si les 2 colonnes du fichier A ne sont pas trouvées dans le fichier B, les 2 colonnes du fichier A sont colorées en rouge.


Tout fonctionne très bien, sauf que la définition de mon Range pose problème, puisqu'il me prend la première ligne (celle d'en-tête avec les filtres).
Par conséquent, les en-tête des deux colonnes sont colorés en rouge, car inclus dans mes plages

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Set plageA = Workbooks(fichier_A).Sheets(feuille_fichier_A).Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
Set plageB = Workbooks(fichier_B).Sheets(feuille_fichier_B).Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)

Pouvez-vous m'aider à attribuer à mes variables plageA et plageB uniquement les cellules filtrées et visibles, SANS la ligne d'en-tête ? S'il vous plait.
Ca peut paraitre tout simple mais je sèche ... malgré mes recherches (j'ai trouvé beaucoup de solutions, mais aucune ne me correspond).

[Philippe PONS]

Salut,

en redimensionnant et en décalant la plage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub test_forum()
    Dim sh As Worksheet
    Dim plageA As Range
    Set sh = Workbooks(fichier_A).Sheets(feuille_fichier_A)
    Set plageA = sh.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
    ' redimensionne la plage(une ligne en moins) et la décale une ligne vers le bas
    Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)
End Sub

[kev59600]

Merci pour ton aide Philippe !

J'avais déjà essayé, mais j'ai une erreur d'exécution :
Erreur d'exécution 1004
Erreur définie par l'application ou par l'objet

Causée par la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)

Grosse incompréhension

[RyuAutodidacte]

Bonjour,

je ne connaissais pas : Range("_FilterDataBase")
mais je viens de tester et ça marche, donc ma question est : as tu bien défini toutes tes variables ??
Du genre : Dim plageA as Range …, etc

[Philippe PONS]

Essaie de chercher la cause en testant chaque partie.
Par exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)
plageA.Select
' puis
Debug.Print plageA.Rows.Count
' puis
 plageA.Resize(plageA.Rows.Count - 1).Select
' tu peux ainsi vérifier si les références sont bonnes

[kev59600]

Merci pour vos réponses !
Oui, j'ai bien défini toutes mes variables, dont les Range


La première sélection fonctionne à merveille, sans surprises, j'ai bien ma place sélectionné correctement (j'avais déjà vérifié de mon côté) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

plageA.Select

Le problème vient du fait que ma plage ne contient qu'une ligne. La procédure suivante me retourne 1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Debug.Print plageA.Rows.Count

D'où l'impossibilité de redimensionner, car on aurait donc 0 lignes en réduisant plageA d'1 ligne.
Cette ligne plante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

plageA.Resize(plageA.Rows.Count - 1).Select

Peut-être une histoire de lignes non-adjacentes ?
Mais alors pourquoi mon FOR EACH fonctionne ? C'est dingue !

[RyuAutodidacte]

Je pense que si tu fais le test avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Debug.print plageA.address

tu verras sûrement comme résultat une plage discontinue, et comme tu le dis l'en-tête est bien pris en compte.
Il est donc normal que ton résultat pour le count soit 1 pour la plage du filtre, enfin je pense (je ne pourrais faire les tests que ce soir)
Je n'ai pas vu ton code mais ton for Each doit passer par l'ensemble des lignes de ton filtre (c'est pourquoi cela fonctionne et que l en-tête est prise en compte)
À mon avis, il faudrait rajouter une condition dans ton for Each pour que la ligne 1 (celle de l'en-tête) soit omise dans la boucle
Test en rajoutant un If et fait moi savoir si c'est OK

[RyuAutodidacte]

J'ai trouvé le pourquoi du comment
Quand l'on actionne un filtre, il cache des lignes, pour montrer le résultat avec les lignes visibles restantes (désolé @Philippe PONS ) après des recherches et des tests on ne peut pas utilisé le Resize et le Offset à sa guise comme une simple Range

Mais pourquoi ? Tout simplement, par ce que le filtre créé quasi tout le temps plusieurs plages discontinuent (j'ai enfin trouvé, ouuuufppphee) que l'on peut intercepter via ce code _FilterDataBase (je viens d'apprendre une nouvelle chose ), un peu comme Union(Range1, Range2, …)
Si quelqu'un peut m'éclairer sur la différence _FilterDataBase vs Union(Range1, Range2, …), merci

Du coup le Filtre créé différentes AIRES (vba : Areas) composées de plusieurs plages, dont l'en-tête est apparemment comprise dans l'écriture du code donné par le demandeur du post : @kev59600
voici les tests :
• 1 - Création d'un nouveau fichier Excel puis copie des données dans Feuil1 -> activer le filtre sur l'en-tête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
En-tête
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

pour le test on va paramétrer le filtre : Inférieur à 5 + cocher ou + Supérieur 10

• 2 - on copie le code dans ThisWorkbook ou dans un module :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub test()
    Dim DerL As Long, RgA_Filtre As Range, RgA_E As Range
 
    DerL = Feuil1.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Rows.Count
    Set RgA_Filtre = Feuil1.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
 
    MsgBox "Nombre de lignes visibles : " & DerL & vbLf & "Nb lignes visibles sans en-tête : " & DerL - 1 '1er test
    MsgBox "Nombre de lignes visibles du filtre : " & RgA_Filtre.Rows.Count & vbLf & "Nb lignes visibles du filtre sans en-tête : " & RgA_Filtre.Rows.Count - 1 '2è test => Ecriture correct pour le filtre
 
    MsgBox "Ensemble des RANGE du filtre, en-tête comprise : " & vbLf & RgA_Filtre.Address
    MsgBox "Ensemble du nbres de lignes(1e RANGE du filtre)" & vbLf & "mais seulement de la 1e AIRE/RANGE : " & vbLf & RgA_Filtre.Rows.Count 'ce qu'il faut en déduire : le filtre créer une multi-plage, un peu à la façon d'Union, mais ce n'est pas Union(range1, range2,…) PS : si meilleurs explications, je prends :)
    'le Count se fait sur la 1ère plage contigu du filtre (filtre = plusieurs plage)
    Set RgA_E = RgA_Filtre.Areas(1).Rows(1)
    MsgBox "Adresse de l'en-tête du filtre : " & RgA_E.Address
 
    For Each Areas In RgA_Filtre.Areas
        x& = x& + 1
        If x = 1 Then
            MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address
        Else
            MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address
        End If
    Next
 
    Set RgA_Filtre = Nothing
    Set RgA_E = Nothing
End Sub

Le résultat parle de lui même à mon avis.

Je pense que cela va aider pas mal de personne, enfin je l'espère, en tout cas pour ma part j'ai appris de nouvelles choses

Voilà

[kev59600]

Je te remercie grandement pour ton aide et le temps accordé (une réponse aussi complète à 02h36, je n'en demandais pas tant)
Grâce à toi je comprends mieux la source de l'erreur !

En effet, si je fais un plageA.Address (que j'avais déjà réalisé puisque j'ai passé pas mal de temps à trouver la source d'erreur) j'ai bien des plages de cellules non adjacentes.

Je vais donc intégrer une condition qui permettra de ne pas considérer la ligne d'en-tête, comme tu me l'indiques.
J'avais aussi songé à cette solution, mais je voulais être fixé quant à la possibilité ou non de combiner Resize et Offset sur mes plages.
J'effectue le test et je reviens vous informer.

[Philippe PONS]

@RyuAutodidacte: pas de problème, je vais aussi apprendre qquechose, quand j'aurais le temps de lire le résultat de tes recherches!
@kev59600: désolé, j'avais, un peu, laissé tombé le fil après ma première intervention.
Bonne journée!

[RyuAutodidacte]

Bonjour

Je vais donc intégrer une condition qui permettra de ne pas considérer la ligne d'en-tête, comme tu me l'indiques.
J'avais aussi songé à cette solution, mais je voulais être fixé quant à la possibilité ou non de combiner Resize et Offset sur mes plages.

Comme tu peux le voir Resize et Offset peuvent s'utiliser avec Areas voir la boucle for Each dans l'exemple.
Si je réponds, j'essaie d'assumer le plus possible, et ça me permet d'apprendre aussi, après je e suis pas infaillible, mais avec les tests,cela m'a paru d'une évidence, de plus tout le monde en profite

[kev59600]

N'étant pas à l'aise avec les Areas, j'ai opté pour un IF. Ce qui donne (après un petit tour de passe-passe pour récupérer le numéro de ligne) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
'Si la ligne parcourue est la ligne d'en-tête, on l'ignore
If Mid(ligneBW.Address, InStrRev(ligneBW.Address, "$") + 1, Len(ligneBW.Address)) = ligne_entete_fichier_bw Then
    trouve = True
    Exit For
End If

Encore merci !

[RyuAutodidacte]

Re,
pour éviter de tomber sur une erreur dans la boucle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
    For Each Areas In RgA_Filtre.Areas
        x& = x& + 1
        If x = 1 And RgA_Filtre.Rows.Count <> 1 Then
            MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address
        Else
            MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address
        End If
    Next

[RyuAutodidacte]

bonsoir,
j'ai fais des petites rectifications de mon code pour que cela soit plus juste (Modif boucle):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub test()
    Dim DerL As Long, RgA_Filtre As Range, RgA_E As Range, x&
 
    DerL = Feuil3.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Rows.Count
    Set RgA_Filtre = Feuil3.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
 
    MsgBox "Nombre de lignes visibles : " & DerL & vbLf & "Nb lignes visibles sans en-tête : " & DerL - 1 '1er test
    MsgBox "Nombre de lignes visibles du filtre : " & RgA_Filtre.Rows.Count & vbLf & "Nb lignes visibles du filtre sans en-tête : " & RgA_Filtre.Rows.Count - 1 '2è test => Ecriture correct pour le filtre
 
    MsgBox "Ensemble des RANGE du filtre, en-tête comprise : " & vbLf & RgA_Filtre.Address
    MsgBox "Ensemble du nbres de lignes(1e RANGE du filtre)" & vbLf & "mais seulement de la 1e AIRE/RANGE : " & vbLf & RgA_Filtre.Rows.Count 'ce qu'il faut en déduire : le filtre créer une multi-plage, un peu à la façon d'Union, mais ce n'est pas Union(range1, range2,…) PS : si meilleurs explications, je prends :)
    'le Count se fait sur la 1ère plage contigu du filtre (filtre = plusieurs plage)
    Set RgA_E = RgA_Filtre.Areas(1).Rows(1)
    MsgBox "Adresse de l'en-tête du filtre : " & RgA_E.Address
 
    x = 1
    For Each Areas In RgA_Filtre
        If x = 1 And RgA_Filtre.Areas(x).Rows.Count > 1 Then 
'Si RgA_Filtre.Areas(x).Rows.Count = 1, cela veut dire qu'il n'y a que l'entête alors on passe au prochain Areas - Si RgA_Filtre.Areas(x).Rows.Count > 1 donc la Range est adjacent à l'en-tête, on se décale d'1 ligne et on Resize -1 ligne pour ne pas prendre l'en-tête
            MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address
        Else
            x = x + 1
            MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address
        End If
        If x = RgA_Filtre.Areas.Count Then Exit For ' Si le nombre de boucle est = au nombre d' Areas dans le filtre, on quitte la boucle
    Next
 
    Set RgA_Filtre = Nothing
    Set RgA_E = Nothing
End Sub