Récupération Range suite Autofilter

Version imprimable

Voir 40 message(s) de cette discussion en une page

11/04/2016, 16h21
cerede2000

1 pièce(s) jointe(s)

Récupération Range suite Autofilter
Bonjour à tous,

J'ai une feuille avec une mise en forme globale (titres diverse, dates, logo de la société...)
Sur la plage B12:N12 j'ai un filtre (pas de tableau Excel, juste un filtre).

Les données présentes sous la ligne de filtre ne sont pas contigus, les utilisateurs aiment laisser des lignes vides avant de ressaisir quelque chose :
Pièce jointe 206611

Le but de la manœuvre est de traité plusieurs classeurs différents ayant la même structure, en récupérant les données pour les regrouper.

J'active donc le filtre en colonne 3 (D) sur non vide :
Code:

1 2 3 With ActiveSheet .Range("$B$12:$N$21").AutoFilter field:=3, Criteria1:="<>" End With
Et je cherche ensuite à faire un Copy =>PasteSpecial xlPasteValues (car je ne souhaites prendre que les valeurs).
Car les plages étant discontinues cela génère plusieurs Area donc plus simple (je trouve de cette manière).

Mon souci, c'est que je n'arrive pas correctement à récupérer uniquement la plage des lignes visibles après filtre.
Avec ceci, j'obtiens bien le nombre de lignes restantes :

Code:

.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

Ce qui me permet de savoir si je doit traiter ou non.

Déjà ce qui m'échappe c'est pourquoi ceci :

Code:

.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count - 1

Ne renvois pas le même résultat !

Ensuite je fais ceci :
Code:

1 2 Dim srcR As Range Set srcR = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
Mais du coup plus possible de retirer la ligne de filtre en faisant :

Code:

Set srcR = srcR.Resize(srcR.Rows.Count - 1).Offset(1)

Puisqu'on se retrouve déjà avec un découpage de plage non contiguë.

Je n'arrive pas à trouver la meilleure méthode pour ne récupérer que les lignes visibles sans la ligne de filtre, le top serais un plage directement non contiguë pour se passer du Copy/Paste et pouvoir directement utiliser le .value de la source et de la destination.

Merci à vous :)
11/04/2016, 16h40
Marc-L

Bonjour,

tenant compte uniquement des cellules visibles,
les méthodes Range.Copy comme Range.Delete ne nécessitent donc pas SpecialCells(xlCellTypeVisible) …
A partir d'une plage filtrée, pas besoin de Paste car Copy en précisant son argument Destination fonctionne sans souci !

La propriété Value ne fait pas le distinguo entre le visible et le caché …

______________________________________________________________________________________________________
Je suis Paris, Charlie, Bruxelles ,…
11/04/2016, 16h42
cerede2000

Arf !
J'ai en effet omis un détail qui a son importance !
Je ne veux que les valeurs et surtout pas la mise en forme :)
D'où le Copy/PasteSpecial ;)

@Marc-L : Tu n'as pas dans un coin un truc efficace pour récupérer correctement la plage des lignes filtrées ? :calim2:
11/04/2016, 16h51
Marc-L

Dans ce cas effectivement c'est un Copy puis un PasteSpecial.

Pour une matrice directe je n'ai rien …
Sinon utiliser la fonction de feuille de calculs SOUS.TOTAL(103 … (SubTotal en VBA)
pour compter le nombre de lignes visibles et dimensionner une variable tableau
pour l'alimenter via une boucle sur les Areas, mais je doute que ce soit plus rapide …

Ou encore pour chaque ligne tester Rows(n°ligne).Hidden …

Hum !
J'ai trouvé quelque chose de satisfaisant !
Code:

1 2 3 4 5 6 .Range("$B$12:$N$21").AutoFilter field:=3, Criteria1:="<>" nbRowsVisible = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 2 If nbRowsVisible > 0 Then Set srcR = .AutoFilter.Range Set srcR = srcR.Resize(srcR.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible) End If
nbRowsVisible est bon et correspond à ce qu'Excel affiche en statusbar.
srcR ne contiens que les lignes visible sans la ligne de filtre, en Areas certes mais étant donné que j'utilise ensuite Copy/PasteSpecial c'est ok !

Je laisse encore ouvert si jamais quelqu'un passant par là à quelque chose d'autre à proposer :)

Code:

1 2 3 With .Range("$B$12:$N$21") .AutoFilter 3, "<>" .Offset(1).Copy
Si j'ai besoin de tester le nombre de lignes visibles, j'utilise SubTotal …

11/04/2016, 17h43
cerede2000

Heu, que se passe-t-il avec ton code si après filtre il n'y a pas de ligne :aie:
11/04/2016, 17h46
Marc-L

Soit en amont j'utilise un CountIf soit après le filtre un SubTotal …
11/04/2016, 17h50
cerede2000
Je ne comprends pas, est-ce que cela pose un soucis ?
Code:

1 2 nbRowsVisible = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 2 If nbRowsVisible > 0 Then
Pourquoi utiliser une fonction Worksheet plutôt qu'un simple test IF ?
11/04/2016, 19h57
RyuAutodidacte

Hi cerede,
Dans le cas où les vides sont inutiles, est-il nécessaire de les garder ?
Si non, pourquoi pas les supprimer directement avant copie ?
J'ai une petite idée qui me vient, mais il faut d'abord que je teste avant de dire une bêtise :mrgreen:
11/04/2016, 20h06
cerede2000

Oula non !
L'utilisateur qui à saisi et qui continuera sa saisie veut que ça reste comme il l'as rentré au départ !
On ne doit rien modifier, juste récupérer les lignes non vide point.

Pas de tri également, impossible au vu des données, ça modifierais ce qu'as saisi l'utilisateur.

Re,
alors voilà ce que je te propose, dans le cas où tu copies toutes tes données de la feuille Toto dans chaque classeur, :mouarf: alors copie les avec les lignes vides => oui oui ;) (donc pas besoin d'utiliser d'autofilter pour les lignes vides)
et sur ton classeur final une fois que tout est copié, utilises ce code (à adapter):

Code:

Columns(4).SpecialCells(xlCellTypeBlanks).Cells.EntireRow.Delete

Sur un post auquel j'ai répondu j'avais donné ce code (cela peut toujours servir) - on peut gerer l'en-tête - mettre le nom du sous-dossier principal pour les classeurs à copier ou avoir le choix d'un autre sous-dossier via un imputbox => copie toutes les donnés en value (Marche sur MAC/PC)
A adapter. Pricncipe : Fichier Maitre (récup tous les classeurs) -> Sous-Dossier TOTO -> Fichier1, Fichier2, Fichier3…
(petit ;) à Marc pour le Application.PathSeparator ; merci)

Code:

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Public sD As Variant, S As String 'à mettre tout en haut avant le 1er Sub
 
Sub BackUp_Filiales()
Dim Rep$, Fichier$, sD_Courant$, Choix_sD$, Rw As Range, R_Rw As Long, C_Rw As Integer
    Application.ScreenUpdating = False
 
    sepRep = Application.PathSeparator
 
    Entete& = 7 'Choisir le nombre de ligne pour l'entête => Entête des fichiers sources se trouvant dans le sous-dossier => pour récup. données
    sD_Courant$ = "Filiales" 'mettre le sous-dossier utilisé couramment
 
    Choix_sD = MsgBox("Voulez-vous utiliser le sous-dossier courant", vbYesNo) 'sD pour "sous-dossier"
 
    If Choix_sD = vbYes Then sD = sD_Courant Else sD = Application.InputBox("Mettre le nom du sous-dossier", "SOUS_DOSSIER")
 
On Error Resume Next
    Dir (ThisWorkbook.Path & sepRep & sD & sepRep)
 
    If sD = False Then
        S = "Annulation de la copie"
        GoTo Reponse
    ElseIf sD = "" Then
        S = "Aucun nom de sous-dossier entré"
        GoTo Reponse
    ElseIf Err.Number <> 0 Then
        S = "Le dossier '" & sD & "' n'existe pas"
        Err.Clear: On Error GoTo 0: GoTo Reponse
    Else
        S = "La MAJ du fichier est OK" & vbCrLf & "Sous-dossier choisi : " & sD
    End If
 
    Rep = ThisWorkbook.Path & sepRep & sD & sepRep
    Fichier = Dir(Rep)
 
    Do While Fichier <> ""
        Workbooks.Open Workbooks(ActiveWorkbook.Name).Path & sepRep & sD & sepRep & Fichier
 
        With Sheets(1)
            Set Rw = .UsedRange.Resize(.UsedRange.Rows.Count - Entete).Offset(Entete)
            R_Rw = Rw.Rows.Count
            C_Rw = Rw.Columns.Count
            Feuil1.UsedRange.Resize(R_Rw, C_Rw).Offset(Feuil1.Range("A" & Rows.Count).End(xlUp).Row).Value = Rw.Value
        End With
 
        Set Rw = Nothing
        Application.Workbooks(Fichier).Close False
        ThisWorkbook.Activate
 
        Fichier = Dir
    Loop
 
    Application.ScreenUpdating = True
 
Reponse:
    MsgBox S
    Application.Goto Cells(1), True
 
End Sub

Voilà j'attends ton retour en espérant avoir répondu à ton problème, sinon dit moi si il y a une donnée que j'ai pas compris ;)

12/04/2016, 02h51
RyuAutodidacte

Re,
En fait, je viens de donner une nouvelle réponse qui pourrait t'intéresser sur ce fil : http://www.developpez.net/forums/d15...ules-variable/, post #8 : http://www.developpez.net/forums/d15...e/#post8596307
12/04/2016, 06h36
cerede2000

Oui supprimer les lignes vides après est une idée que je vais tester !

Par contre la solution que tu donne dans l'autre topic ne corresponds pas à mon besoin, en effet à partir du moment ou il y à autre chose sur la feuille (mise en page avec logo titre qui ne peuvent pas être placé dans l'entête), ça devient plus compliqué.

Merci pour tes réponses ;)
12/04/2016, 07h52
Philippe Tulliez

Bonjour,
Pour ma part, que ce soit pour exporter ou supprimer des lignes d'une liste de données suivant critères, j'utilise les filtres avancés (méthode AdvancedFilter) avec un critère nommé. Bien plus simple à mettre en place en VBA que le filtre simple je trouve.
L'avantage supplémentaire c'est que pour connaître le nombre qui répond aux critères, on peut utiliser une fonction de la catégorie Base de données qui fonctionne de la même manière que les filtres avancéss avec zone des données et zone des critères.
12/04/2016, 08h32
cerede2000

Plus simple l'AdvancedFilter ?
Déjà le temps de filtre est plus long !
En plus il faut définir une zone pour mettre les critères....

@RyuAutodidacte : J'men doutais un peu, ceci ne fonctionne pas :

Code:

.SpecialCells(xlCellTypeBlanks).Cells

Tout simplement parce que il y à des cellules vide sur des lignes qui elles ne sont pas entièrement vides.....
12/04/2016, 08h45
Philippe PONS

Hello,

à noter que cette zone de critères, tu peux la définir sur une autre feuille, pour ne pas
toucher à la feuille qui contient les données.
Cordialement,
12/04/2016, 08h46
cerede2000

Yes, bien sur, mais il faut la créé car mes classeurs ne contiennent qu'une seule feuille.
Et comme indiqué quand je vois le temps de filtre..... Comparé à un filtre classique pour lequel on décoche Vides, il n'y a pas photo c'est hallucinant !
12/04/2016, 10h04
RyuAutodidacte

Hi,

Citation:

Envoyé par cerede2000

Tout simplement parce que il y à des cellules vide sur des lignes qui elles ne sont pas entièrement vides.....

Tu veux dire qu'il y a comme un(e) résidu/valeur fantôme dans les cellule vides??

Citation:

Envoyé par RyuAutodidacte

Hi,

Tu veux dire qu'il y a comme un(e) résidu/valeur fantôme dans les cellule vides??

Non pas du tout, mais dans une ligne, certaines cellules ne sont pas remplis, donc cette ligne dégage également, car dans la sélection xlCellTypeBlanks une des cellules de cette ligne est comprise !

Bon ben je reste sur :
Code:

1 2 3 4 .Range("$B$12:$N$21").AutoFilter field:=3, Criteria1:="<>" nbRowsVisible = .AutoFilter.Range.Columns(3).SpecialCells(xlCellTypeVisible).Count - 1 Set srcR = .AutoFilter.Range Set srcR = srcR.Resize(srcR.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
Qui me donne entière satisfaction, autant sur le nombre de ligne qu'il reste (nbRowsVisible) que sur la plage finale sélectionné sans la ligne d'entête :)

Voir 40 message(s) de cette discussion en une page