XL2007 filtre et gros fichiers

Le
le Nordiste
Bonjour,

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

Je désire, aprés un filtrage automatique, connaitre la première lign=
e
filtrée et la dernirère ligne filtrée.
Mes deux fonctions ci-dessous donnent parfois des réponses
incohérentes.

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

mais même comme cela j'ai parfois des réponses incohérentes : ligne 1
…

Comment fiabiliser mes deux procédures ?

Voici mes deux fonctions :
'--=

Function PremièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA PREMIERE LIGNE FILTRÉE

Dim strColonne As String 'lettre de la
colonne à l'extréme droite
Dim boolFlgFiltre As Boolean 'mémorisation de la
valeur de AutoFilterMode

On Error Resume Next
PremièreLigneFiltrée = 1 'valeur par défaut si
résulktat du filtre est vide

With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
If .Areas(1).Rows.Count > 1 Then
PremièreLigneFiltrée = .Rows(2).Row
Else
PremièreLigneFiltrée = .Areas(2).Row '!!!!! si aucune
ligne dans le résultat erreur 1004
End If
End With

'End Function
'=
==

Function DernièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA DERNIERE LIGNE FILTRÉE


With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
DernièreLigneFiltrée = .Areas(.Areas.Count)
(.Areas(.Areas.Count).Count).Row
End With

End Function
'=
==
==
=


Merci de votre aide !
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 2
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
DanielCo
Le #23295031
Bonjour,
Apparemment, les fonctions a des soucis avec SpecialCells; le même code
fonctionne dans une macro et déraille dans une fonction :
Function Prem()
With ActiveSheet.AutoFilter
Set plage = .Range.Offset(1).Resize(.Range.Rows.Count - 1)
Set plage = plage.SpecialCells(xlCellTypeVisible)
MsgBox plage.Row
End With
End Function
et
Sub test()
With ActiveSheet.AutoFilter
Set plage = .Range.Offset(1).Resize(.Range.Rows.Count - 1)
Set plage = plage.SpecialCells(xlCellTypeVisible)
MsgBox plage.Row
End With
End Sub
contiennent le même code; l'une fonctionne, l'autre pas. Comprenne qui
pourra...
Daniel


Bonjour,

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

Je désire, aprés un filtrage automatique, connaitre la première ligne
filtrée et la dernirère ligne filtrée.
Mes deux fonctions ci-dessous donnent parfois des réponses
incohérentes.

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

mais même comme cela j'ai parfois des réponses incohérentes : ligne 1


Comment fiabiliser mes deux procédures ?

Voici mes deux fonctions :
'-----------------------------------------------------------------------------------------------------------------------------
Function PremièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA PREMIERE LIGNE FILTRÉE

Dim strColonne As String 'lettre de la
colonne à l'extréme droite
Dim boolFlgFiltre As Boolean 'mémorisation de la
valeur de AutoFilterMode

On Error Resume Next
PremièreLigneFiltrée = 1 'valeur par défaut si
résulktat du filtre est vide

With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
If .Areas(1).Rows.Count > 1 Then
PremièreLigneFiltrée = .Rows(2).Row
Else
PremièreLigneFiltrée = .Areas(2).Row '!!!!! si aucune
ligne dans le résultat erreur 1004
End If
End With

'End Function
'======================================================================== > Function DernièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA DERNIERE LIGNE FILTRÉE


With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
DernièreLigneFiltrée = .Areas(.Areas.Count)
(.Areas(.Areas.Count).Count).Row
End With

End Function
'========================================================================== >

Merci de votre aide !
DanielCo
Le #23295051
Essaie celle-ci pour la première ligne :

Function Prem()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set plage = .Range.Resize(, 1)
For i = 2 To .Range.Rows.Count
Var = plage(i).EntireRow.RowHeight
If plage(i).EntireRow.RowHeight <> 0 Then
Prem = plage(i).Row
Exit For
End If
Next i
End If
End With
End Function

(je n'ai pas mis de routine d'erreur).
Daniel


Bonjour,
Apparemment, les fonctions a des soucis avec SpecialCells; le même code
fonctionne dans une macro et déraille dans une fonction :
Function Prem()
With ActiveSheet.AutoFilter
Set plage = .Range.Offset(1).Resize(.Range.Rows.Count - 1)
Set plage = plage.SpecialCells(xlCellTypeVisible)
MsgBox plage.Row
End With
End Function
et
Sub test()
With ActiveSheet.AutoFilter
Set plage = .Range.Offset(1).Resize(.Range.Rows.Count - 1)
Set plage = plage.SpecialCells(xlCellTypeVisible)
MsgBox plage.Row
End With
End Sub
contiennent le même code; l'une fonctionne, l'autre pas. Comprenne qui
pourra...
Daniel


Bonjour,

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

Je désire, aprés un filtrage automatique, connaitre la première ligne
filtrée et la dernirère ligne filtrée.
Mes deux fonctions ci-dessous donnent parfois des réponses
incohérentes.

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

mais même comme cela j'ai parfois des réponses incohérentes : ligne 1


Comment fiabiliser mes deux procédures ?

Voici mes deux fonctions :
'-----------------------------------------------------------------------------------------------------------------------------
Function PremièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA PREMIERE LIGNE FILTRÉE

Dim strColonne As String 'lettre de la
colonne à l'extréme droite
Dim boolFlgFiltre As Boolean 'mémorisation de la
valeur de AutoFilterMode

On Error Resume Next
PremièreLigneFiltrée = 1 'valeur par défaut si
résulktat du filtre est vide

With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
If .Areas(1).Rows.Count > 1 Then
PremièreLigneFiltrée = .Rows(2).Row
Else
PremièreLigneFiltrée = .Areas(2).Row '!!!!! si aucune
ligne dans le résultat erreur 1004
End If
End With

'End Function
'======================================================================== >> Function DernièreLigneFiltrée()
'RETOURNE LE NUMÉRO DE LA DERNIERE LIGNE FILTRÉE


With ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
DernièreLigneFiltrée = .Areas(.Areas.Count)
(.Areas(.Areas.Count).Count).Row
End With

End Function
'========================================================================== >>

Merci de votre aide !
DanielCo
Le #23295071
L'ennui, c'est que ça risque d'être long...
Daniel

Essaie celle-ci pour la première ligne :

Function Prem()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set plage = .Range.Resize(, 1)
For i = 2 To .Range.Rows.Count
Var = plage(i).EntireRow.RowHeight
If plage(i).EntireRow.RowHeight <> 0 Then
Prem = plage(i).Row
Exit For
End If
Next i
End If
End With
End Function

(je n'ai pas mis de routine d'erreur).
Daniel
DanielCo
Le #23295291
A tester à fond :
1. création de deux noms :
Sub test()
ThisWorkbook.Names.Add "PremLigne", RefersTo:=1
ThisWorkbook.Names.Add "DerLigne", RefersTo:=1
End Sub
2 dans une cellule de la feuille, mettre :
=SOUS.TOTAL(103;A:A)
remplacer la colonne A par une colonne du tableau filtré.
Dans le module de la feuille, mettre :

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With ActiveSheet.AutoFilter.Range
Set plage = .Offset(1).Resize(.Rows.Count -
1).SpecialCells(xlCellTypeVisible)
End With
ThisWorkbook.Names("PremLigne").RefersTo = plage.Row
With plage.Areas(plage.Areas.Count)
Set Var = plage.Areas(plage.Areas.Count)
ThisWorkbook.Names("DerLigne").RefersTo = .Row + .Rows.Count -
1
End With
Application.EnableEvents = True
End Sub

Les première et dernière lignes sont données par :
=PremLigne
et
ÞrLigne
Daniel


L'ennui, c'est que ça risque d'être long...
Daniel

Essaie celle-ci pour la première ligne :

Function Prem()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set plage = .Range.Resize(, 1)
For i = 2 To .Range.Rows.Count
Var = plage(i).EntireRow.RowHeight
If plage(i).EntireRow.RowHeight <> 0 Then
Prem = plage(i).Row
Exit For
End If
Next i
End If
End With
End Function

(je n'ai pas mis de routine d'erreur).
Daniel
le Nordiste
Le #23295411
DanielCo ton code ci-dessous fonctionne bien -jusqu'à ce que je
découvre un bogue.

Function Prem()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set plage = .Range.Resize(, 1)
For i = 2 To .Range.Rows.Count
Var = plage(i).EntireRow.RowHeight
If plage(i).EntireRow.RowHeight <> 0 Then
Prem = plage(i).Row
Exit For
End If
Next i
End If
End With
End Function

Reste à revoir pour la dernière ligne maintenant
le Nordiste
Le #23295401
Reste à revoir pour la dernière ligne maintenant



Function Der()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set Plage = .Range.Resize(, 1)
For i = .Range.Rows.Count To 2 Step -1
Debug.Print .Range.Rows.Count
Var = Plage(i).EntireRow.RowHeight
If Plage(i).EntireRow.RowHeight <> 0 Then
Der = Plage(i).Row
Exit For
End If
Next i
End If
End With

End Function


Même remarque que ci-dessus,
C'est long supposons que ce soit du bon.

Je suis preneur de toutes améliorations et suggestions pour gagner du
temps.

Merci de m'avoir lu et entendu !
DanielCo
Le #23295441
Reste à revoir pour la dernière ligne maintenant



Function Der()
With ActiveSheet.AutoFilter
If .Range.Rows.Count > 1 Then
Set Plage = .Range.Resize(, 1)
For i = .Range.Rows.Count To 2 Step -1
Debug.Print .Range.Rows.Count
Var = Plage(i).EntireRow.RowHeight
If Plage(i).EntireRow.RowHeight <> 0 Then
Der = Plage(i).Row
Exit For
End If
Next i
End If
End With

End Function


Même remarque que ci-dessus,
C'est long supposons que ce soit du bon.

Je suis preneur de toutes améliorations et suggestions pour gagner du
temps.

Merci de m'avoir lu et entendu !



Regarde mon dernier post.
Daniel
MichD
Le #23295651
Bonjour,

Une autre alternative :

Si c'est dans une procédure :

'-------------------------------------------
Sub test()
Dim PremLig As Long, DerLig As Long

With Worksheets("Feuil1") 'Nom feuille à adapter
With .Range("B1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
PremLig = .Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row

DerLig = .Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
End With
MsgBox "Première ligne : " & PremLig & vbCrLf & _
"Dernière ligne : " & DerLig
End Sub
'-------------------------------------------

2 fonctions maintenant pour trouver la première et la dernière ligne
'-----------------------------------------------------
Function Filtre_PremLig(Rg As Range)
Dim PremLig As Long
On Error Resume Next
With Rg
PremLig = .Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row
End With
If Err <> 0 Then
Err = 0
Filtre_PremLig = "Plage vide"
Else
Filtre_PremLig = PremLig
End If
End Function
'-----------------------------------------------------
Function Filtre_DerLig(Rg As Range)
Dim DerLig As Variant
On Error Resume Next
With Rg
DerLig = .Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
If Err <> 0 Then
Err = 0
Filtre_DerLig = "Plage vide"
Else
Filtre_DerLig = DerLig
End If
End Function
'-----------------------------------------------------

Dans une cellule devant afficher le résultat :
=Filtre_DerLig(A:A) OU =Filtre_PremLig(A:A)
tu adaptes la plage de cellules au besoin


MichD
--------------------------------------------
MichD
Le #23295731
Bonjour Daniel,

Effectivement j'ai déjà noté que sur des très grandes plages de cellules,
la méthode "SpecialCells" éprouvait des difficultés. Je crois me souvenir
que cela avait surtout rapport au nombre de plages discontinues contenues
dans la sélection. Après un certain nombre, elle ne tient plus compte du
reste de la plage de cellules. Cela semble être une limitation de la méthode.

Est-ce qu'il y a une différence entre l'utilisation de la méthode dans une
procédure ou une fonction, je n'ai jamais fait de test en ce sens là... est-ce
que le filtre était le même ? À investiguer les jours de pluie ! ;-))


MichD
--------------------------------------------
DanielCo
Le #23295811
Bonjour Denis,
Non, j'ai testé avec un tableau de dix lignes et j'ai bien une
différence entre la macro et la fonction.
Daniel


Bonjour Daniel,

Effectivement j'ai déjà noté que sur des très grandes plages de cellules,
la méthode "SpecialCells" éprouvait des difficultés. Je crois me souvenir
que cela avait surtout rapport au nombre de plages discontinues contenues
dans la sélection. Après un certain nombre, elle ne tient plus compte du
reste de la plage de cellules. Cela semble être une limitation de la méthode.

Est-ce qu'il y a une différence entre l'utilisation de la méthode dans une
procédure ou une fonction, je n'ai jamais fait de test en ce sens là...
est-ce que le filtre était le même ? À investiguer les jours de pluie ! ;-))


MichD
--------------------------------------------
Publicité
Poster une réponse
Anonyme