Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe autofilter rijen op basis van de celwaarde in Excel?

Normaal gesproken kan de filterfunctie in Excel ons helpen om alle gegevens te filteren zoals we dat nodig hebben, maar soms wil ik cellen automatisch filteren op basis van een handmatige celinvoer. Dit betekent dat wanneer ik een criterium in een cel invoeg, de gegevens kunnen worden automatisch gefilterd. Zijn er goede ideeën om deze taak in Excel aan te pakken?

Automatische filterrijen op basis van de celwaarde die u hebt ingevoerd met VBA-code

Gegevens filteren op basis van meerdere criteria of andere specifieke voorwaarden, zoals tekstlengte, hoofdlettergevoelig


Automatische filterrijen op basis van de celwaarde die u hebt ingevoerd met VBA-code


Stel dat ik het volgende gegevensbereik heb, nu, als ik de criteria opneem in cel E1 en E2, wil ik dat de gegevens automatisch worden gefilterd zoals onderstaand screenshot laat zien:

doc automatisch filter 1

1. Ga naar het werkblad waarvan u de datum automatisch wilt filteren op basis van de celwaarde die u hebt ingevoerd.

2. Klik met de rechtermuisknop op het bladentabblad en selecteer Bekijk code uit het contextmenu, in de popped out Microsoft Visual Basic voor toepassingen venster, kopieer en plak de volgende code in de blanco module venster, zie screenshot:

VBA-code: automatische filtergegevens op basis van de ingevoerde celwaarde:

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 automatisch filter 2

Notes: In de bovenstaande code, A1: C20 is uw gegevensbereik dat u wilt filteren, E2 is de doelwaarde waarop u wilt filteren op basis van, en E1: E2 is uw criterium waarvan de cel zal worden gefilterd op basis van. U kunt ze naar uw behoefte veranderen.

3. Nu, wanneer u de criteria in cel invoert E1 en E2 en druk op invoeren sleutel, worden uw gegevens automatisch gefilterd op de celwaarden.


Gegevens filteren op basis van meerdere criteria of andere specifieke voorwaarden, zoals tekstlengte, hoofdlettergevoelig

Gegevens filteren op basis van meerdere criteria of een andere specifieke voorwaarde, zoals tekstlengte, hoofdlettergevoeligheid, enzovoort.

Kutools for Excel's Super filter functie is een krachtig hulpprogramma, u kunt deze functie toepassen om de volgende bewerkingen te voltooien:

  • Gegevens filteren met meerdere criteria; Gegevens filteren op tekstlengte;
  • Gegevens filteren op hoofdletters / kleine letters; Filter datum op jaar / maand / dag / week / kwartaal

doc-super-filter1

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Demo: Auto-filterrijen op basis van de celwaarde die u hebt ingevoerd met VBA-code


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Werkt met Office 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    tim · 27 days 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 · 22 days 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 · 1 months 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 · 1 months 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 · 1 months 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.
    Robert · 6 months 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 · 7 months 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.
    murat yazici · 7 months 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 · 7 months 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.
    Kent · 7 months ago
    The VB script worked beautifully. Many thanks for the post!
  • To post as a guest, your comment is unpublished.
    Bob · 7 months 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 · 7 months 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.
    Mark · 9 months 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 · 8 months 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.
        Mark · 8 months 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 · 10 months ago
    its working and awsome...thanks
  • To post as a guest, your comment is unpublished.
    George · 1 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 · 2 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 · 2 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 · 1 years ago
      In Range("E2").Address delete any input. All will "unfilter."
  • To post as a guest, your comment is unpublished.
    ZZted · 2 years ago
    How do I undo it?it hides all of my data.
  • To post as a guest, your comment is unpublished.
    jdvdp · 2 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 · 2 years ago
    THANK YOU SO MUCH FOR THE ABOVE FORMULA - IT WORKS GREAT.