Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

XL2007 filtre et gros fichiers

20 réponses
Avatar
le Nordiste
Bonjour,

J'utilise excel 2007 pour traiter de gros fichiers : plus de 100 000
lignes.

Je d=E9sire, apr=E9s un filtrage automatique, connaitre la premi=E8re lign=
e
filtr=E9e et la dernir=E8re ligne filtr=E9e.
Mes deux fonctions ci-dessous donnent parfois des r=E9ponses
incoh=E9rentes.

J'avais des difficult=E9 quand le r=E9sultat du tri est trop morcel=E9 :
j'avais lu quelque spart une notion d'"AREA".
Pour r=E9soudre cela je tri judisieusement pour avoir le minimum
d'"areas"

mais m=EAme comme cela j'ai parfois des r=E9ponses incoh=E9rentes : ligne 1
=85

Comment fiabiliser mes deux proc=E9dures ?

Voici mes deux fonctions :
'--------------------------------------------------------------------------=
---------------------------------------------------
Function Premi=E8reLigneFiltr=E9e()
'RETOURNE LE NUM=C9RO DE LA PREMIERE LIGNE FILTR=C9E

Dim strColonne As String 'lettre de la
colonne =E0 l'extr=E9me droite
Dim boolFlgFiltre As Boolean 'm=E9morisation de la
valeur de AutoFilterMode

On Error Resume Next
Premi=E8reLigneFiltr=E9e =3D 1 'valeur par d=E9faut si
r=E9sulktat du filtre est vide

With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
If .Areas(1).Rows.Count > 1 Then
Premi=E8reLigneFiltr=E9e =3D .Rows(2).Row
Else
Premi=E8reLigneFiltr=E9e =3D .Areas(2).Row '!!!!! si aucune
ligne dans le r=E9sultat erreur 1004
End If
End With

'End Function
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Function Derni=E8reLigneFiltr=E9e()
'RETOURNE LE NUM=C9RO DE LA DERNIERE LIGNE FILTR=C9E


With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
Derni=E8reLigneFiltr=E9e =3D .Areas(.Areas.Count)
(.Areas(.Areas.Count).Count).Row
End With

End Function
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D


Merci de votre aide !

10 réponses

1 2
Avatar
MichD
Ne répondant qu'à ma curiosité,

La méthode specialCells n'est pas utilisable dans une fonction personnalisée.
Ça règle le problème.

Dans une procédure standard,
Sous Excel 2007,

Si je mets un chiffre une ligne sur 2 dans la plage A1:A80000
la dernière ligne sélectionnée sera : A65535 peu importe le
nombre de lignes après...le nombre de plages discontinues, ne
semble pas affectée ce résultat. En fait, elle tient compte du
nombre de lignes des versions Excel précédentes...Elle n'a pas
été mise à jour...!

'---------------------------------------
Sub test()
Dim Rg As Range
Set Rg = Range("A1:A65535")
Rg.SpecialCells(xlCellTypeConstants, 1).Select
End Sub
'---------------------------------------
La dernière ligne sélectionnée est : 65535

Comme ma plage a des chiffres qu'une ligne sur deux, si je lui
fais évaluer le nombre "Areas" dans la plage, le maximum qu'elle
peut me retourner est : 8192 . En conséquence, elle ne tient compte
seulement des 16384 premières cellules. Si le lui demande d'effectuer
le calcul sur 16385 cellules, elle me retourne 1. Dans cet exercice,
la limitation est vraiment les 8192 plages discontinues plutôt que
de l'étendue de la plage de cellules, car si les 500 premières lignes
ne contiennent pas de plages discontinues, les 16384 lignes augmenteront
jusqu'au moment où elle aura atteint 8192 plages discontinues.
'--------------------------------------------
Sub test()
Dim Rg As Range
Dim X As Range, A As Long, Adr As String
Set Rg = Range("A1:A16384")
Set X = Rg.SpecialCells(xlCellTypeConstants, 1)
A = X.Areas.Count
End Sub
'--------------------------------------------
8192 -->> 2^13
16384 ->> 2^14
65535 -->> 2^16

Ce qui précède semble être une limitation de la méthode. Conséquemment
attention aux manipulations sur de grandes plages impliquant

La méthode specialCells est à oublier dans une fonction personnalisée
Une fonction personnalisée n'intervient pas au niveau de la feuille de
calcul. Elle ne peut faire des sélections... elle n'évalue que le résultat
savant de petits calculs ou manipulations de chaînes de caractères.

Voilà, si d'autres veulent effectuer d'autres tests et nous faire part de leurs
découvertes, ils sont les bienvenus.

MichD
--------------------------------------------
Avatar
DanielCo
J'ai du mal à te suivre, Denis; la plage A1:A1000000 est remplie une
cellule sur 2 et filtrée. La ligne :
Var =
ActiveSheet.[A1:A1000000].SpecialCells(xlCellTypeVisible).Areas.Count
renvoie 500 000 (Excel 2010).
Daniel


Ne répondant qu'à ma curiosité,

La méthode specialCells n'est pas utilisable dans une fonction personnalisée.
Ça règle le problème.

Dans une procédure standard,
Sous Excel 2007,

Si je mets un chiffre une ligne sur 2 dans la plage A1:A80000
la dernière ligne sélectionnée sera : A65535 peu importe le
nombre de lignes après...le nombre de plages discontinues, ne
semble pas affectée ce résultat. En fait, elle tient compte du
nombre de lignes des versions Excel précédentes...Elle n'a pas
été mise à jour...!

'---------------------------------------
Sub test()
Dim Rg As Range
Set Rg = Range("A1:A65535")
Rg.SpecialCells(xlCellTypeConstants, 1).Select
End Sub
'---------------------------------------
La dernière ligne sélectionnée est : 65535

Comme ma plage a des chiffres qu'une ligne sur deux, si je lui
fais évaluer le nombre "Areas" dans la plage, le maximum qu'elle
peut me retourner est : 8192 . En conséquence, elle ne tient compte
seulement des 16384 premières cellules. Si le lui demande d'effectuer
le calcul sur 16385 cellules, elle me retourne 1. Dans cet exercice,
la limitation est vraiment les 8192 plages discontinues plutôt que
de l'étendue de la plage de cellules, car si les 500 premières lignes
ne contiennent pas de plages discontinues, les 16384 lignes augmenteront
jusqu'au moment où elle aura atteint 8192 plages discontinues.
'--------------------------------------------
Sub test()
Dim Rg As Range
Dim X As Range, A As Long, Adr As String
Set Rg = Range("A1:A16384")
Set X = Rg.SpecialCells(xlCellTypeConstants, 1)
A = X.Areas.Count
End Sub
'--------------------------------------------
8192 -->> 2^13
16384 ->> 2^14
65535 -->> 2^16

Ce qui précède semble être une limitation de la méthode. Conséquemment
attention aux manipulations sur de grandes plages impliquant

La méthode specialCells est à oublier dans une fonction personnalisée
Une fonction personnalisée n'intervient pas au niveau de la feuille de
calcul. Elle ne peut faire des sélections... elle n'évalue que le résultat
savant de petits calculs ou manipulations de chaînes de caractères.

Voilà, si d'autres veulent effectuer d'autres tests et nous faire part de
leurs découvertes, ils sont les bienvenus.

MichD
--------------------------------------------
Avatar
MichD
| J'ai du mal à te suivre, Denis

Je dirais plutôt que tu as du mal à lire... dans ma réponse,
je spécifiais la version Excel 2007.
;-))

En fait, si j'écris :

Range("A1:A1000000").SpecialCells(xlCellTypeConstants, 1).Select
Il sélectionne toute la plage de cellules sans se soucier de la restriction

En fait, Excel 2007 ne peut pas aller au-delà de 16384 si une cellule
sur 2 est vide, ce qui donne 8192 plages discontinues. Ce qui semble
problématique c'est le dépassement de 8192 plages discontinues plutôt
que l'étendue de la plage elle-même.
Range("A1:A16384").SpecialCells(xlCellTypeConstants, 1).Select

J'en profite pour corriger le message précédent. La sélection ne se limite
pas à 65536 cellules, mais à 16384 si le nombre de plages discontinues est
de 8192. Au-delà, la restriction n'est pas respectée et toutes les cellules de
la plage sont sélectionnées.
C'est aussi vrai pour le décompte du nombre "Areas" dans la plage
x = Range("A1:A16384").SpecialCells(xlCellTypeConstants, 1).Areas.count
au-delà de 8192 on obtient 1.

Après vérification, ce sont les mêmes normes en vigueur que sur les versions
Excel 2003. Si cette restriction ne tient plus en 2010, c'est une nouveauté.

Voilà !



MichD
--------------------------------------------
"DanielCo" a écrit dans le message de groupe de discussion : iok91d$84k$

J'ai du mal à te suivre, Denis; la plage A1:A1000000 est remplie une
cellule sur 2 et filtrée. La ligne :
Var ActiveSheet.[A1:A1000000].SpecialCells(xlCellTypeVisible).Areas.Count
renvoie 500 000 (Excel 2010).
Daniel


Ne répondant qu'à ma curiosité,

La méthode specialCells n'est pas utilisable dans une fonction personnalisée.
Ça règle le problème.

Dans une procédure standard,
Sous Excel 2007,

Si je mets un chiffre une ligne sur 2 dans la plage A1:A80000
la dernière ligne sélectionnée sera : A65535 peu importe le
nombre de lignes après...le nombre de plages discontinues, ne
semble pas affectée ce résultat. En fait, elle tient compte du
nombre de lignes des versions Excel précédentes...Elle n'a pas
été mise à jour...!

'---------------------------------------
Sub test()
Dim Rg As Range
Set Rg = Range("A1:A65535")
Rg.SpecialCells(xlCellTypeConstants, 1).Select
End Sub
'---------------------------------------
La dernière ligne sélectionnée est : 65535

Comme ma plage a des chiffres qu'une ligne sur deux, si je lui
fais évaluer le nombre "Areas" dans la plage, le maximum qu'elle
peut me retourner est : 8192 . En conséquence, elle ne tient compte
seulement des 16384 premières cellules. Si le lui demande d'effectuer
le calcul sur 16385 cellules, elle me retourne 1. Dans cet exercice,
la limitation est vraiment les 8192 plages discontinues plutôt que
de l'étendue de la plage de cellules, car si les 500 premières lignes
ne contiennent pas de plages discontinues, les 16384 lignes augmenteront
jusqu'au moment où elle aura atteint 8192 plages discontinues.
'--------------------------------------------
Sub test()
Dim Rg As Range
Dim X As Range, A As Long, Adr As String
Set Rg = Range("A1:A16384")
Set X = Rg.SpecialCells(xlCellTypeConstants, 1)
A = X.Areas.Count
End Sub
'--------------------------------------------
8192 -->> 2^13
16384 ->> 2^14
65535 -->> 2^16

Ce qui précède semble être une limitation de la méthode. Conséquemment
attention aux manipulations sur de grandes plages impliquant

La méthode specialCells est à oublier dans une fonction personnalisée
Une fonction personnalisée n'intervient pas au niveau de la feuille de
calcul. Elle ne peut faire des sélections... elle n'évalue que le résultat
savant de petits calculs ou manipulations de chaînes de caractères.

Voilà, si d'autres veulent effectuer d'autres tests et nous faire part de
leurs découvertes, ils sont les bienvenus.

MichD
--------------------------------------------
Avatar
DanielCo
Une bonne nouvelle alors ! on a un peu trop tendance à assimiler XL2007
et XL2010. J'avais bien noté ta version, et c'est pourquoi j'ai indiqué
la mienne.
Daniel


J'ai du mal à te suivre, Denis



Je dirais plutôt que tu as du mal à lire... dans ma réponse,
je spécifiais la version Excel 2007.
;-))

En fait, si j'écris :

Range("A1:A1000000").SpecialCells(xlCellTypeConstants, 1).Select
Il sélectionne toute la plage de cellules sans se soucier de la restriction

En fait, Excel 2007 ne peut pas aller au-delà de 16384 si une cellule
sur 2 est vide, ce qui donne 8192 plages discontinues. Ce qui semble
problématique c'est le dépassement de 8192 plages discontinues plutôt
que l'étendue de la plage elle-même.
Range("A1:A16384").SpecialCells(xlCellTypeConstants, 1).Select

J'en profite pour corriger le message précédent. La sélection ne se limite
pas à 65536 cellules, mais à 16384 si le nombre de plages discontinues est
de 8192. Au-delà, la restriction n'est pas respectée et toutes les cellules
de la plage sont sélectionnées.
C'est aussi vrai pour le décompte du nombre "Areas" dans la plage
x = Range("A1:A16384").SpecialCells(xlCellTypeConstants, 1).Areas.count
au-delà de 8192 on obtient 1.

Après vérification, ce sont les mêmes normes en vigueur que sur les
versions
Excel 2003. Si cette restriction ne tient plus en 2010, c'est une
nouveauté.

Voilà !



MichD
--------------------------------------------
Avatar
le Nordiste
Bonjour � tous et particuli�rement � MichD,


Je lance les deux proc�dures de MichD par :

Sub lancement()

MsgBox Filtre_PremLig(activesheet.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(activesheet.Range("A2:AJ106420"))

End Sub

j'ai gliss� dans chacune un Rg.Select pour v�rifier que cel a passe
bien. jusque l� OK

Mais PremLigne comme DerLigne restent tout deux � vide => en reto ur
"Plage Vide"

merci de votre aide, compr�hension , patience,� (Choisi ssez trois
parmi les trois)
Avatar
MichD
Bonjour,

J'ai refait un petit test, et j'obtiens bien le résultat escompté.
La ligne 1 comporte les étiquettes de colonne puisque tu fais un filtre...

Est-ce possible que la feuille active soit vide au moment de lancer la procédure ?
Je n'ai pas vraiment d'autres explications! Est-ce que tu as autre chose dans
cette plage de données que des données ?

Personnellement, j'utiliserais plutôt l'une de deux syntaxes suivantes afin
de t'assurer que les procédures traitent une plage de cellules sur la feuille désirée.

'--------------------------------------------
Sub lancement()
With Worksheets("Feuil1")
MsgBox Filtre_PremLig(.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(.Range("A2:AJ106420"))
End With
End Sub
'--------------------------------------------
'OU si tu veux affranchir la procédure d'un changement
'possible du nom de l'onglet de la feuille, pourquoi
'ne pas utiliser la propriété "Name" de l'objet feuille
'visible seulement dans la fenêtre de l'éditeur de code ?
'--------------------------------------------
Sub lancement1()
With Feuil1
MsgBox Filtre_PremLig(.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(.Range("A2:AJ106420"))
End With
End Sub
'--------------------------------------------



MichD
--------------------------------------------
"le Nordiste" a écrit dans le message de groupe de discussion :


Bonjour � tous et particuli�rement � MichD,


Je lance les deux proc�dures de MichD par :

Sub lancement()

MsgBox Filtre_PremLig(activesheet.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(activesheet.Range("A2:AJ106420"))

End Sub

j'ai gliss� dans chacune un Rg.Select pour v�rifier que cela passe
bien. jusque l� OK

Mais PremLigne comme DerLigne restent tout deux � vide => en retour
"Plage Vide"

merci de votre aide, compr�hension , patience,� (Choisissez trois
parmi les trois)
Avatar
le Nordiste
On 20 avr, 12:10, "MichD" wrote:
Bonjour,

J'ai refait un petit test, et j'obtiens bien le résultat escompté.
La ligne 1 comporte les étiquettes de colonne puisque tu fais un filtre ...

Est-ce possible que la feuille active soit vide au moment de lancer la pr océdure ?
Je n'ai pas vraiment d'autres explications! Est-ce que tu as autre chose dans
cette plage de données que des données ?

Personnellement, j'utiliserais plutôt l'une de deux syntaxes suivantes afin
de t'assurer que les procédures traitent une plage de cellules sur la f euille désirée.

'--------------------------------------------
Sub lancement()
With Worksheets("Feuil1")
    MsgBox Filtre_PremLig(.Range("A2:AJ106420"))
    MsgBox Filtre_DerLig(.Range("A2:AJ106420"))
End With
End Sub
'--------------------------------------------
'OU si tu veux affranchir la procédure d'un changement
'possible du nom de l'onglet de la feuille, pourquoi
'ne pas utiliser la propriété "Name" de l'objet feuille
'visible seulement dans la fenêtre de l'éditeur de code ?
'--------------------------------------------
Sub lancement1()
With Feuil1
    MsgBox Filtre_PremLig(.Range("A2:AJ106420"))
    MsgBox Filtre_DerLig(.Range("A2:AJ106420"))
End With
End Sub
'--------------------------------------------

MichD
--------------------------------------------
"le Nordiste"  a écrit dans le message de groupe de discussion :


Bonjour tous et particuli rement MichD,

Je lance les deux proc dures de MichD par :

Sub lancement()

MsgBox Filtre_PremLig(activesheet.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(activesheet.Range("A2:AJ106420"))

End Sub

j'ai gliss dans chacune un Rg.Select pour v rifier que cela passe
bien.  jusque l OK

Mais PremLigne comme DerLigne restent tout deux vide => en retour
"Plage Vide"

merci de votre aide, compr hension , patience,     (Choisissez trois
parmi les trois)



J'ai repris par copier coller et changé le nom de feuille
'--------------------------------------------
Sub lancement()
With Feuil2
MsgBox Filtre_PremLig(.Range("A2:AJ106420"))
MsgBox Filtre_DerLig(.Range("A2:AJ106420"))
End With
End Sub
'--------------------------------------------




Toujours le même soucis.

Dans ma feuille je n'ai que des données numérique ou texte ou des
cellules vides.
J'ai commencé à remplir les cellules des 2 lignes inférieures : pas
d'amélioration.

Ce qui est curieux c'est que les codes :
With Rg
PremLig = .Find(What:="*", LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
DerLig = .Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRo ws,
SearchDirection:=xlPrevious).Row
end with

retournent Vide comme si la plage n'etait pas définie.
Avatar
MichD
Si tu veux, publie qu'une toute petite partie de ta feuille,
tu peux y mettre des données fictives si tu veux...
Pour ce faire, utilise l'adresse cijoint.fr et tu nous retournes ici
l'adresse que tu auras obtenue.



MichD
--------------------------------------------
Avatar
le Nordiste
On 20 avr, 13:41, "MichD" wrote:
Si tu veux, publie qu'une toute petite partie de ta feuille,
tu peux y mettre des donn es fictives si tu veux...
Pour ce faire, utilise l'adresse cijoint.fr et tu nous retournes ici
l'adresse que tu auras obtenue.

MichD
--------------------------------------------



J'ai jamais utilisé ci-joint.
Y a t'il une procédure quelque part ?
Avatar
MichD
Dans la fenêtre de cijoint.Fr, Un clic sur le bouton parcourir
pour retrouver ledit fichier dans l'explorateur Windows

ne pas oublier de cocher la case "Rendre le fichier public"

et tu cliques sur le bouton "cliquer pour déposer le fichier ici"

Quelques instants plus tard apparaîtra une adresse, et c'est celle-ci
que tu dois publier ici



MichD
--------------------------------------------
"le Nordiste" a écrit dans le message de groupe de discussion :


On 20 avr, 13:41, "MichD" wrote:
Si tu veux, publie qu'une toute petite partie de ta feuille,
tu peux y mettre des donn es fictives si tu veux...
Pour ce faire, utilise l'adresse cijoint.fr et tu nous retournes ici
l'adresse que tu auras obtenue.

MichD
--------------------------------------------



J'ai jamais utilisé ci-joint.
Y a t'il une procédure quelque part ?
1 2