Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment filtrer automatiquement les lignes en fonction de la valeur de la cellule dans Excel?

Normalement, la fonction Filtrer dans Excel peut nous aider à filtrer toutes les données dont nous avons besoin, mais, parfois, je voudrais filtrer automatiquement les cellules en fonction d'une entrée de cellule manuelle, ce qui signifie que lorsque j'entre un critère dans une cellule, les données peuvent être filtré automatiquement à la fois. Y a-t-il de bonnes idées pour gérer ce travail dans Excel?

Filtrer automatiquement les lignes en fonction de la valeur de cellule que vous avez entrée avec le code VBA

Filtrer les données en fonction de plusieurs critères ou d'une autre condition spécifique, comme la longueur du texte, en respectant la casse


Filtrer automatiquement les lignes en fonction de la valeur de cellule que vous avez entrée avec le code VBA

En supposant que j'ai la plage de données suivante, maintenant, lorsque j'entre les critères dans les cellules E1 et E2, je veux que les données soient filtrées automatiquement comme ci-dessous la capture d'écran montrée:

doc filtre automatique 1

1. Accédez à la feuille de calcul pour laquelle vous souhaitez filtrer automatiquement la date en fonction de la valeur de cellule que vous avez entrée.

2. Cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez Voir le code dans le menu contextuel, dans le popped out Microsoft Visual Basic pour applications fenêtre, veuillez copier et coller le code suivant dans l'espace vide Module fenêtre, voir capture d'écran:

Code VBA: filtre automatique des données en fonction de la valeur de cellule saisie:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

doc filtre automatique 2

Notes: Dans le code ci-dessus, A1: C20 est votre plage de données que vous souhaitez filtrer, E2 est la valeur cible sur laquelle vous souhaitez filtrer, et E1: E2 est votre cellule de critères sera filtrée en fonction de. Vous pouvez les modifier selon vos besoins.

3. Maintenant, lorsque vous entrez les critères dans la cellule E1 et E2 et appuyez sur Entrer clé, vos données seront automatiquement filtrées par les valeurs de cellule.


Filtrer les données en fonction de plusieurs critères ou d'une autre condition spécifique, comme la longueur du texte, en respectant la casse

Filtrez les données en fonction de plusieurs critères ou d'une autre condition spécifique, telle que la longueur du texte, la casse, etc.

Kutools pour Excel's Super filtre fonctionnalité est un utilitaire puissant, vous pouvez appliquer cette fonctionnalité pour terminer les opérations suivantes:

  • Filtrer les données avec plusieurs critères; Filtrer les données par longueur de texte;
  • Filtrer les données par majuscules / minuscules; Filtrer la date par année / mois / jour / semaine / trimestre

Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!


Démo: filtrer automatiquement les lignes en fonction de la valeur de cellule que vous avez entrée avec le code VBA


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ofer Hirshfeld · 1 years ago
    hi

    thank you - this code was a life saver

    but if I wish to filter when the value on E2 contains the search text.
    so if i search for Grade1
    it should filter
    Grade1
    ExampleGrade1
    Grade1ETC


    many thanks in advance
  • To post as a guest, your comment is unpublished.
    Aaron · 1 years ago
    I scanned through the comments and didn't see a specific answer. I am looking to filter a Table that changes based on a DB query, The table range will change every time the query is refreshed, How can we account for this in the code? The function becomes pointless if I have to direct users edit the VBA code every time they refresh the table.
  • To post as a guest, your comment is unpublished.
    Henk · 1 years ago
    Good day,

    I have made sheet1 with the table in the example. In sheet2 all the data are referenced to the table in sheet1.

    In sheet2 I can choose the grades exactly as in the example. That works OK.

    However when I change a Grade of a student in sheet1 the list is not updated in sheet2. So the filter does not work automatic.

    How can this be done?
  • To post as a guest, your comment is unpublished.
    minnie · 1 years ago
    Hello, is there a way to quickly modify the VBA code to filter on values bigger than in selected cell?
  • To post as a guest, your comment is unpublished.
    Stan · 1 years ago
    Hi there,

    Thank you for the great content, however I am having some issues and was wondering whether you had any ideas why.

    I have adjusted my ranges so my code reads:

    Sub AutoFilter()
    If Target.Address = Range("E13:F14").Address Then
    Worksheets('Data Archive').Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("F10:F1048576")
    End If
    End Sub


    However, when I run the macro a "Compile Error: Syntex Error" message appears.

    Thank you in advance

    Stan
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Stan,
      Do you set the data range and criteria range correctly in the code?Or you can take your data range as a screenshot here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Vincent VBA · 1 years ago
    Thank you very much for this. It worked for me fine to a certain extent, please let me explain:

    I have multiple tables in my workbook. When I applied the above code, it only worked for one table, but not for the other two tables. Let's say the first table is, as in your example, from A1:C20. The second table is from A22:C40. The third from A42:C60. All tables have the info about the "Grade", however they have different columns & Date and therefore cannot be consolidated into one big tables but rather 3 smaller tables.

    How would the code need to like like so that if I put the criteria: "Grade1", all 3 tables will be automatically filtered for "Grade1" ?

    Thank you very much in advance.

    Best regards
  • To post as a guest, your comment is unpublished.
    Peter Johansen · 1 years ago
    Thank you, for this, it was really helpful. i just have one question, after selected the name in the drop down menu (i have names as criteria), it only shows people with that name, as it should, but how can i do so after i have selected a name, then i want so see all the rows of my table??

    hope you can help me out.

    kind regards
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Peter,
      This code is worked well for fitering the entire rows of the data, could you enter your table range correctlly?

      A1:C20 is the range of your data, E1:E2 is the criteria range.
      Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
  • To post as a guest, your comment is unpublished.
    tim · 2 years ago
    Hey guys,
    perfect Explanation, thank you very much.
    1 Little question: if I want to filter with 2,3 4 or more criterias how do I do this?
    For example I want to say I wanna see the Name Henry, with Grade 1 and this Age...so not just 1 criteria but for example 3..=?


    thanks for the respond


    Kind regards,


    TIM
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, tim,
      To auto filter data based on multiple criteria, you should apply the below code: (please change the cell references to your need)

      Private Sub Worksheet_Change(ByVal Target As Range)
      'Update by Extendoffice
      Dim xVStr As String
      Dim xFStr As String
      xVStr = "E22:G22" 'the criteria that you want to filter based on
      xFStr = "E21:G22" 'the range contains the header of the criteria
      If Not (Intersect(Range(xVStr), Target) Is Nothing) Then
      Range("A1:C17").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
      CriteriaRange:=Range(xFStr)
      End If
      End Sub


      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Bogdan · 2 years ago
    Hello,

    What if I got the filtered data in a different tab(sheet 2) in the same workbook and the cell that the filter needs to refer to is in the first tab(sheet 1). I used this VBA but is not working like that, only if I have both the criteria cell(E2 in this VBA) in the same tab with the filtered data(A1:C20)
  • To post as a guest, your comment is unpublished.
    mjr_awesome · 2 years ago
    There might be a mistake in the instructions. Instead of pasting the code into a blank Module, one should paste it into the Sheet window. For example, if the macro is to work on Sheet1, the code should be pasted into Microsoft Excel Objects -> Sheet1(Sheet1). Only then it works for me on Excel 2016.

    Thanks for the code!
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, mjr,
      There is no mistake in this article, the article said, you should put the VBA code into the sheet module by right click the sheet name and then choose View Code to go to the module.
      But, your operation is correct as well.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    rphil123 · 2 years ago
    So I have a bunch of values and then a table of data. I am wondering if I can filter that table based on the values similarly to what is explained above. For example I would like to click on a cell that has the value of 3, which corresponds to 3 records(200 rows, 25 columns) that meet a condition and then have my table filtered to just show those records. An example of a condition would be, if one variable is great than 100. I have over 100 of these conditions which is why I would like my table to be linked to it in some way. Any help would be much appreciated. In your example provided, it would be similar to if you just wanted all ages over 3, 6, 9, 12 etc and then you had 25 similar variables.So to filter the table to show only records with age over 3 based on clicking a value from a list that says something like age>3 - 2 records, age>6 - 4 records etc
  • To post as a guest, your comment is unpublished.
    Elliott · 2 years ago
    Is there a way to have it continue to filter with additional boxes. When I write it as ElseIf, it only follows the ElseIf command.
  • To post as a guest, your comment is unpublished.
    mrtyzc61 · 2 years ago
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice 20160606
    If Target.Address = Range("E2").Address Then
    Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
    End If
    End Sub


    E2 HUCRESI YERINE E SUTUNUNUNA YAZILAN SON SATIRA GORE FILITRELEME YAPABILIR MI


    According the code mentioned above , is it possible to make filtration according the written data to the last row of column E ?


    I hope to get help and thanks for your help
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, murat,
      The above code works well in the whole worksheet, you just need to change the cell references to your need. Please try it, thank you!
      • To post as a guest, your comment is unpublished.
        mrtyzc61 · 2 years ago
        I don't know how can I change the cell reference.
  • To post as a guest, your comment is unpublished.
    Kent · 2 years ago
    The VB script worked beautifully. Many thanks for the post!
  • To post as a guest, your comment is unpublished.
    Bob · 2 years ago
    What happens if you have GRADE11 and GRADE12 for example. Will the filter show these also if you try and filter
    on GRADE1?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Bob,
      Yes, as you said, when entering part of the text you want to filter, all the cells contain the part text will be filtered out. So, if you type Grade1, all cells contain Grade1, Grade11, Grage123...will be filtered out.
  • To post as a guest, your comment is unpublished.
    Dispatcher7 · 3 years ago
    Thank you for this code. I have been trying to modify it to work better for me, but having difficulty.

    My sheet has data from A2:G2280 Column A contains street names. I want to be able to type at least part of the street name into A1 and display only data that contains A1 in all or part. So if I type Bro in A1 I would see the rows that have Broad, Broadway and Brook. Of course if A1 is blank I would see everything.



    Sorry I'm not fluent in the Excel VBA lingo, I'm just a 911 dispatcher that knows their is an easier way.



    Thank you.



    Mark
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Mark,
      To solve your problem, please apply the following VBA code:
      Note: In the below code, the A1 is the cell that you want to enter the criteria, A2:D20 is the data range, A is the column contains the criteria that you want to filter from, please change the cell references to your own.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      Dim xRRg As Range
      Dim xFNum As Integer
      On Error Resume Next
      If Target.Address <> Range("A1").Address Then Exit Sub
      Set xRg = Range("A2:D20").CurrentRegion
      Application.ScreenUpdating = False
      If Target.Text = "" Then
      xRg.Rows.Select
      Selection.EntireRow.Hidden = False
      Application.ScreenUpdating = True
      Exit Sub
      End If
      For xFNum = 1 To xRg.Rows.Count
      Set xRRg = xRg.Range("A" & xFNum)
      xRRg.Rows.Select
      If InStr(xRRg.Text, Target.Text) > 0 Then
      Selection.EntireRow.Hidden = False
      Else
      Selection.EntireRow.Hidden = True
      End If
      Next xFNum
      Application.ScreenUpdating = True
      End Sub

      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Carsten · 1 years ago
        Hi skyyang,
        your code works perfectly, but I have a question: If I don't want to have a filter criterion in cell A1 and leave the cell empty, it will no longer be displayed any line. So everything is filtered out. Just like Mark wanted "Of course if A1 is blank I would see everything" it doesn't work, does it?
        Would be nice if you could help me.
        Greetings
        Carsten
        • To post as a guest, your comment is unpublished.
          skyyang · 1 years ago
          Hi, Carsten,
          When leaving the cell A1 empty, all the data rows are displayed, when entering any character you want to filter, it will work correctly. How is your problem, please insert your problem sheet as screenshot here.

          Thank you!
      • To post as a guest, your comment is unpublished.
        Carsten · 1 years ago
        Hi skyyang,
        your code works perfectly, but I have a question: If I don't want to have a filter criterion in cell A1 and leave the cell empty, it will no longer be displayed any line. So everything is filtered out. Just like Mark wanted "Of course if A1 is blank I would see everything" it doesn't work, does it?
        Would be nice if you could help me.
        Greetings
        Carsten
      • To post as a guest, your comment is unpublished.
        Dispatcher7 · 3 years ago
        Thanks for the help.
        I changed A2:D20 to A3:G2281 to represent my data field. Now when I type anything in cell A1 and tab out of the cell rows 2-109 are hidden. It is not filtering and displaying only rows that contain all or in part what is entered in cell A1.



        Any ideas?
  • To post as a guest, your comment is unpublished.
    shahbaaz · 3 years ago
    its working and awsome...thanks
  • To post as a guest, your comment is unpublished.
    George · 3 years ago
    Thank you for this write up! I am trying to adjust the code to allow a range of acceptance.

    Example: I input 5 and it filters and only shows everything that is within .5 of 5, (so 4.5 to 5.5)
  • To post as a guest, your comment is unpublished.
    Javier · 4 years ago
    Doesn't work for me, might be that I have office 2010? doesn't do anything :S
  • To post as a guest, your comment is unpublished.
    Amanda · 4 years ago
    Hi,

    The code below works perfectly. However, how do I disable the macro if I want to unfilter?
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice 20160606
    If Target.Address = Range("E2").Address Then
    Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      KST · 3 years ago
      In Range("E2").Address delete any input. All will "unfilter."
  • To post as a guest, your comment is unpublished.
    ZZted · 4 years ago
    How do I undo it?it hides all of my data.
  • To post as a guest, your comment is unpublished.
    jdvdp · 4 years ago
    I've been trying to filter a worksheet with a variety of codes (taken from various sites, including this one), but none seem to work. In a sheet with information in the cell range A101:EF999 (yes, big one), I want to autofilter the sheet based on a three letter code that I enter into cell B5, which should correspond to rows having that same code in column B101-B999. A sample snippet would look like this:

    A B C D E
    5 ABC
    ...
    101 ABC
    102 DEF
    103 GHI
    104 ABC
    105 JKL
    106 ABC
    107 DEF

    On selecting "ABC" in cell B5, only rows 101, 104 and 106 should be displayed, but nothing happens. Is there something I'm overlooking here? Any help would be much appreciated!
  • To post as a guest, your comment is unpublished.
    Jon · 5 years ago
    THANK YOU SO MUCH FOR THE ABOVE FORMULA - IT WORKS GREAT.