EXCEL_All Versions

Afficher la boîte de dialogue filtre élaboré : Application.dialogs(xlDialogFilterAdvanced).show
Pour un champ donné de la liste : Application.dialogs(xlDialogFilter).show N
... où N représente le numéro du champ (numéro de colonne) pour lequel

Afficher la boîte de dialogue "Filtre automatique personnalisé" pour un champ donné de la liste

Application.dialogs(xlDialogFilter).show N, où N représente le numéro du champ (numéro de colonne) pour lequel tu veux afficher la boîte de dialogue des critères de filtrage
personnalisés.

Base filtrée ou non

If ActiveSheet.AutoFilter Is Nothing Then MsgBox "Aucun filtre"
(if activesheet.autofiltermode=true then selection.autofilter)

Filtrer les données si elles ne sont pas filtrées, toutes les afficher si elles sont filtrées

With ActiveSheet
If Not .AutoFilterMode Then .Range("C10").AutoFilter Else
.ShowAllData
End With

Copier la base filtrée sur une feuille : sheets("Feuil1").range("_FilterDatabase").Copy sheets("Feuil2").range("A1")
Voir aussi les tests d'existence sur les filtres : existence.htm
Propriété AutoFilterMode : a la valeur true quand les flèches de filtre sont visibles
Intercepter le changement de critères : news

Exemple de filtrage

range("A12").autofilter 10, range("A1") Demander le champ à trier et le critère

Sub MonTrie()
Dim MaVarx
Dim MaVary
MaVarx= InputBox("Entrer la valeur «Field» ")
MaVary = InputBox("Entrer la valeur «Criterial» ")
Selection.autofilter Field:=MaVarx, Criteria1:=MaVary
End Sub
Selection.autofilter 3, "10", xlBottom10Percent 'to only display the lowest 10 percent of the Quantities.

Nombre d'enregistrements dans une liste filtrée :

With range("_FilterDatabase").rows
Records_Total = .Count - 1
Records_Found = .SpecialCells(xlVisible).Count - 1
End With

ou

pour des valeurs numériques ou non
="" & sous.total(3;A5:A23) & " of " & nb.val (A5:A23)

ou encore pour des valeurs numériques seulement : ="" & Sous.total(2;B5:B23) & " of " & nb(B5:B23)

Filtrage de date :

Pour que le filtre fonctionne sur les dates, il faut que le critère corresponde très précisément au format de date utilisé dans la plage.
Par exemple, si tes dates sont formatées en "jj.mm.aa", il faut que Criteria1 contienne une chaîne formatée en "jj.mm.aa" (et non pas "jj.mm.aaaa").

donc, si tu es *sûr* que ta liste de dates va toujours garder le format "jj.mm.aa": ActiveCell.autofilter 2, Format$(ActiveCell, "dd.mm.yy")

Et si tu veux que la macro s'adapte automatiquement au format utilisé (préférable à mon avis): ActiveCell.autofilter 2, Format$(ActiveCell, ActiveCell.NumberFormat

ou encore, cela fonctionne aussi comme ça : ActiveCell.autofilter 2, ActiveCell.Text

autre exemple : range("A12").autofilter 10, ">=" & Format("30/04/2000", "mm/dd/yyyy")

Il est possible d'extraire des données qui ne sont pas comprises entre 2 dates

Avec les filtres automatiques par exemple ?
Menu données | Filtre | filtre automatique
Clique sur la petite flèche à côté du titre de la variable à filtrer
choisi "Personnalisé"
Dans 1e 1er champ, sélectionne "inférieur à" et tape une date dans le
champ voisin
Dans 1e 2eme champ, sélectionne "supérieur à" et tape une date dans le
champ voisin

Peut-on connaître le numéro de la première et la dernière ligne trouvée après application d'un critère de filtre auto ?

Dim PremLigne As long, DerLigne As long
With sheets("Feuil1").autofilter.range.SpecialCells(xlCellTypeVisible)
If .Areas(1).rows.Count > 1 Then PremLigne = .rows(2).Row _
Else PremLigne = .Areas(2).Row
DerLigne = .Areas(.Areas.Count)(.Areas(.Areas.Count).Count).Row
MsgBox "Première ligne visible : " & PremLigne
MsgBox "Dernière ligne visible : " & DerLigne
End With

With range("A:A").SpecialCells(xlCellTypeVisible).Areas
If .Item(1).rows.Count > 1 Then range("A2").Select _
Else .Item(2)(1).Select
End With

Récupérer les valeurs non nulles et les copier sur une autre feuille

Sub NonVides()
Application.screenupdating = False
With range("A1", Cells(range("A1")([A:A].Count).End(xlUp).Row, [A1].End(xlToRight).Column))
.autofilter 1, "<>"
Worksheets.Add
.Copy [A1]
End With
End Sub

Faire la somme d'une colonne dans une macro lorsque le tableau possède un filtre automatique avec justement un filtre en cours ? [ mardi 18 avril 2000 21:10, "Re: Calcul dans un tableau "Filtré""]

Dim Somme As double
Dim NumCol As Integer ' Numéro de la colonne du filtre à sommer
NumCol = 2
With sheets("Feuil1").autofilter.range
Somme = Worksheetfunction.Subtotal(9, _
.Item(2, NumCol).Resize(.rows.Count))
End With

=SUBTOTAL(3,INDIRECT("A6:A" & COUNTIF(A:A,"<>")+4)) & " of " & COUNTIF(A:A,"<>")-1

Stocker l'adresse et le critère des filtres appliqués !!!
'The following example stores the address and filtering criteria for the current filtering and then applies new filters.

Dim w As Worksheet
Dim filterArray()
Dim currentFiltrange As String

Sub ChangeFilters()

Set w = Worksheets("Crew")
With w.autofilter
currentFiltrange = .range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With
w.autofilterMode = False
w.range("A1").autofilter field:=1, Criteria1:="S"
End Sub

Connaître le nombre d'enregistrements satisfaisant plusierus critères

Par formule
{=SOMME((Données_Grec="Débutant")*(Données_Latin="Intermédiaire")*(Données_L
V2="Esp"))}
Ou faire un filtre automatique:
Choisir les critères dans les différents champs
Regarder la barre d'état qui affichera le nombre d'enregistrements trouvés
sur le nombre total d'enregistrements
Ou encore faire un filtre élaboré
Etablir une table des critères
Filtrer la liste
Utiliser les fonctions de Base de données

PROBLEMES RELATIFS AU FILTRE

le nombre d'enregistrements n'apparaît plus dans la barre d'état
Solution de André B. dans le message "Re: filtre automatique : nb d'enregistrements dans la barre d'état", le lundi 7 août 2000 22:42 dans le forum microsoft.public.fr.excel :
Ce problème n'existait pas semble-t-il dans les versions antérieures à Excel 97. Pour qu'Excel affiche à chaque fois le nombre d'enregistrements trouvés et non "mode filtre", il faut désactiver le calcul automatique (outils/options/calcul/sur ordre). Fort heureusement, ce choix n'affecte que la feuille en cours, ce qui est judicieux.

Nombre de valeurs différentes dans une zone filtrée : {=NB.DIFF(SOUS.TOTAL(9;DECALER(Rge;SUITE(Rge);;1)))-(SOUS.TOTAL(3;Rge)<NB(Rge))}

En vba, nombre d'enregistrements pour un numéro donné (stocké dans le 3ième champ)

Sub sélection_pour_un_critère()
Dim LeCritère As Integer
LeCritère = InputBox(" quel est le No. de code ? ")
Range("C1").Select 'codes
Selection.AutoFilter Field:=3, Criteria1:=LeCritère
Selection.CurrentRegion.Select
Dim x As Integer
x = Application.Subtotal(3, Range("C:C")) - 1
MsgBox ("Résultat trouvé = " & x & " lignes")
End Sub

De: Céline B.
Objet: Re: Calcul du nombre de personnes remplissant plusieures conditions
Date : samedi 3 juin 2000 04:23

1 )Cliquez n'importe où dans vos données ;
2 )Déroulez le menu Données, pointez sur Filtre et choisissez Filtre automatique ; (Des listes déroulantes sont apparues à droite de vos titres de colonnes)
3) Cliquez sur le triangle de la liste Site et choisissez Paris ;
4) Cliquez sur le triangle de la liste Sexe et choisissez Homme ; (Des lignes sont masquées. Les lignes affichées en bleu correspondent aux critères : Paris et sexe masculin. Pour connaître le nombre de personnes qui répondent à ces deux critères, vous devez insérer deux lignes au-dessus de vos données.)
5) Dans la cellule A1, tapez la formule =SOUS.TOTAL(3;L(3)C:L(1000)C) : Le nombre 3 pour compter le nombre de cellules non-vides, L(1000) nombre déterminé par le nombre d'enregistrements que vous prévoyez saisir, multiplié par 2 pour éviter d'en manquer.
La fonction SOUS.TOTAL peut aussi faire la somme (9), la moyenne, etc.
La fonction Filtre automatique et la formule SOUS.TOTAL sont faciles à mettre en oeuvre et font des miracles.

Autre exemple

A quoi cela peut-il servir !?

Afficher un message pour dire que la saisie est erronée et indiquer de quel type elle doit être
Personnaliser le message en cas d'erreur
Afficher une liste de valeurs possibles

CONTROLE DE LA VALIDITE DES DONNEES

Empêcher la saisie de doublons : sélectionner une colonne, puis exécuter la commande Données/ Validation, personnaliser l'autorisation avec la formule suivante : =NON(NB.SI(A:A;A1)>1)

Personnaliser le message excel qui apparaît lorsque on tente de modifier une cellule d'une feuille protégée (demandé par Jean-Pierre L.) :
Jean-Pierre avait essayé à l'aide de VBA mais excel affiche son message avant que l'évènement _OnChange ne soit atteint & l'instruction Application.DisplayAlerts = False ne fonctionne pas pour ce cas particulier. Laurent a pensé à cela :
- Sélectionne toutes les cellules de la feuille (Ctrl-A)
- Fais Format -> Cellules -> Protection et désactive l'option "Verrouillée"
- Fais Données -> Validation
- Choisis comme critère "Personnalisé"; dans la zone Formule, tape FAUX
- Entre ton message personnalisé dans l'onglet "Message d'erreur", puis OK
- Protège la feuille
Le message d'erreur sera celui de la validation. Comme toutes les cellules sont déverrouillées, le message standard ne s'affichera pas, et comme le critère de validation est FAUX, il sera impossible à l'utilisateur de modifier le contenu des cellules.

Autoriser la saisie en A1 si et seulement si A3 et A5 ne sont pas vides

Solution hors macro par Robert D. : choisir une validation personnalisée et inscrire la formule suivante : =ET(ESTVIDE(A3);ESTVIDE(A5))
=> si A3 et A5 ne sont pas vides, alors vous ne pourrez pas valider une nouvelle saisie pour A1. ... Devinez la différence avec OU au lieu de ET)

Solution macro par J@CAvec une macro (dans la feuille de code attachée à la feuille de calcul contenant les cellules cibles) :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target <> "" And Not Intersect(Target, Range("A1,A3,A5")) Is Nothing Then
If Range("A1,A3,A5").SpecialCells(xlCellTypeBlanks).Cells.Count < 2 Then
Target = ""
End If
End If
End Sub
'quand on change une des cellules A1, A3, ou A5 (la cellule target), la macro teste le nombre de cellule vide dans A1 A3 A5 ; s'il y en moins de 2, alors la macro efface ce qui vient d'être écrit dans la cellule Target

Comment forcer la saisie de nombres entiers
Réponse: la formule =SI(ENT(A1)=A1;"entier";"pas entier") permet de savoir si A1 contient un entier ou non. Accessoirement, la fonction
ENT(A1) permet de retourner la valeur entière de A1. Autre possibilité, utiliser l'outil de validation (Données / Validation), autoriser les données de type entier seulement
Empêcher de saisir plus de deux décimales/ Prevent from using more than 2 decimals. (Text can still be entered)

=IF(ISNUMBER(A1),LEN(A1)-IF(ISERROR(FIND(".",A1)),LEN(A1)-2,FIND(".",A1))<=2 ,TRUE)
=SI(ESTNUM(A1);NBCAR(A1)-SI(ESTERREUR(TROUVE(",";A1));NBCAR(A1)-2;TROUVE(",";A1))<=2;VRAI)

Pour ne pas autoriser la saisie de texte :

=IF(ISNUMBER(A1),LEN(A1)-IF(ISERROR(FIND(".",A1)),LEN(A1)-2,FIND(".",A1))<=2 ,FALSE)
=si(estnum(A1);nbcar(A1)-si(esterreur(trouve(",";A1)),nbcar(A1)-2;trouve(",";1))<=2 ;faux)

Comment faire en sorte que le premier caractère, le second et le troisième caractère d'un code à trois chiffres saisi en un, soit respectivement issu des plages B1:B4,C1:C4;D1:D4 [ses plages contenant un caractère chacune] : utiliser la formule suivante dans une validation personnalisée sur la cellule A1.
=SI((MAX(1*(STXT($A$1;1;1)=$B$1:$B$4))+MAX(1*(STXT($A$1;2;1)=$C$1:$C$4))+MAX(1*(STXT($A$1;3;1)=$D$1:$D$4)))=3;VRAI;FAUX) =IF((MAX(1*(MID($A$1,1,1)=$B$1:$B$4))+MAX(1*(MID($A$1,2,1)=$C$1:$C$4))+MAX(1 *(MID($A$1,3,1)=$D$1:$D$4)))=3,TRUE,FALSE) 'version finale par Tom Ogivly

Limiter la saisie de dates de personnes à des personnes non majeures & donc indiquer un message si la personne n'est pas majeur
Choisir des données de type date, puis indiquer les dates de départ et de fin suivantes :
minimum =DATE(ANNEE(AUJOURDHUI())+18;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())-1)
maximum = maintenant()-1 'version finale par AV

ou encore minimum =maintenant()-1
et date fin = maintenant()+ 18 * 365

Je cherche à repérer les éléments d'une liste QUI NE SONT PAS dans l'autre liste
Soit une liste de valeurs dans les cellules D19 à D25 et une liste de valeurs nommée ou non auxquelles je vais comparer les premières.
Sélectionner la première liste et activer la cellule D19 à l'aide de la touche ctrl et du clic gauche !!!
Aller dans format mise en forme conditionnelle, utiliser la formule suivante : =(EQUIV($D19;liste2;0)) & attribuer un format (exemple : texte barré)
Valider & voilà !

Pour empêcher la saisie de valeurs autres que celles déjà présentes dans la liste nommée, faites exactement la même chose avec la commande validation/données. Vous pouvez aussi utilsier la formule : =INDIRECT(nomplagenommée)

Par macro
With Range("maplagenommée1").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & Range("maplagenommée2").Address
End With

Liste déroulante variables :
Soit trois listes de valeurs Valeurs1, Valeurs2 et Valeurs3, comment faire appel dans les cellules A1, A2 & A3 aux listes respectives :
Sélectionner A1, utiliser la boîte Données/Validation, choisissez "Autoriser" et inscrivez = INDIRECT("Valeurs" & ligne()), puis recopier A1 sur A2 & A3
[la liste des index des valeurs peut très bien être dans d'autres cellules]

Liste déroulante liée à un autre classeur (il doit être ouvert pour que cela fonctionne), par Catherine
On peut faire référence à une liste nommée se trouvant dans un classeur différent. Créer d'abord la liste nommée LaListe dans une feuille du fichier Source.xls
Dans l'autre classeur, créer un nom ListeExterne par Insertion Nom Définir faisant référence à Source.xls!LaListe. Ensuite, dans Données Validation, il suffit de donner =ListeExterne comme source des éléments de la liste de validation.
Le fichier Source doit cependant être ouvert pour pouvoir choisir dans la liste de validation. Il est bien mentionné dans la liste des fichiers de Edition Liaisons comme une source de données externes.

Exemple d'un contrôle des valeurs par macro

Sub Validate1()
Dim rownum, colnum As Integer
Set rng = Application.Caller
If Intersect(rng, Range("D7:H20")) Is Nothing Then Exit Sub
' If Intersect(rng, Range("D7:G20")) Then <=Does nothing, not needed
With rng
If IsNumeric(.Value) Then
If .Value < 0 Or .Value > 3 Then
MsgBox "Entry must be between 0 and 3."
.Value = ""
.Select
End If
Else
MsgBox "Entry must be an integer between 0 and 3."
.Value = ""
.Select
End If
End With
End Sub

Dans la fenetre donnees validation comment interdire d'effacer et bloquer une nouvelle saisie ? On peut interdire une donnee mais je ne sais pas interdire la touche suppression (demandé par Sabato, réponse apportée par Laurent L.): il faut passer par une macro évènementielle :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VType As Long, Cell As Range
On Error Resume Next
For Each Cell In Target
VType = Cell.Validation.Type
If VType And IsEmpty(Cell) Then Exit For
Next Cell
If VType = 0 Or Not IsEmpty(Cell) Then Exit Sub
On Error GoTo 0
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Le contenu de la cellule " & Cell.Address & " ne doit pas être effacé", vbInformation
End Sub

CREATION DE LISTES DE VALEURS

sélectionner la cellule où vous voulez une liste déroulante de valeurs
aller dans Données/Validation
dans l'onglet Options de la boîte de dialogue, choisissez l'option d'autoriser une liste comme critère de validation
décochez éventuellement l'option "Ignorer les blancs" et cocher l'option "Liste déroulante dans la cellule" pour qu'elle apparaisse (cochée par défaut)
saisissez dans une plage de cellule les valeurs qui apparaîtront dans la liste si ce n'est pas déjà fait
indiquer dans le champ source de la boîte de dialogue Validation, soit l'adresse de la plage contenant ces valeurs, soit le nom d'une plage nommée (=maplagenommée) ou bien les valeurs elles-mêmes séparées par un point-virgule mais sans signe égal devant.
paramétrez ce que vous voulez d'autres, puis cliquez sur OK
les valeurs apparaissent quand vous cliquez sur la flèche déroulante.