提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何基于Excel中的单元格值自动筛选行?

通常情况下,Excel中的Filter函数可以帮助我们根据需要过滤任何数据,但有时我想根据手动单元格输入自动过滤单元格,这意味着当我在单元格中输入条件时,数据可能会立即自动过滤。 在Excel中有没有什么好的想法来处理这个工作?

根据您使用VBA代码输入的单元格值自动筛选行

按照多个标准或其他特定条件过滤数据,例如按文本长度区分大小写


根据您使用VBA代码输入的单元格值自动筛选行


假设,我现在有以下数据范围,当我在单元格E1和E2中输入条件时,我希望数据将被自动过滤,如下图所示:

doc自动过滤器1

1。 转到您要根据您输入的单元格值自动过滤日期的工作表。

2。 右键单击工作表选项卡,然后选择 查看代码 从上下文菜单中,弹出 Microsoft Visual Basic for Applications 窗口,请将以下代码复制并粘贴到空白处 模块 窗口,看截图:

VBA代码:根据输入的单元格值自动过滤数据:

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自动过滤器2

注意:在上面的代码中, A1:C20 是你想要过滤的数据范围, E2 是您要基于和过滤的目标值 E1:E2 是你的标准单元格将被基于过滤。 你可以改变他们到你的需要。

3。 现在,当你在单元格中输入标准时 E1 E2 并按下 输入 键,您的数据将自动被单元格值过滤。


按照多个标准或其他特定条件过滤数据,例如按文本长度区分大小写

按照多个标准或其他特定条件过滤数据,例如按文本长度,区分大小写等。

Kutools for Excel超级过滤器 功能是一个强大的实用程序,您可以应用此功能来完成以下操作:

  • 用多个标准过滤数据; 按文本长度过滤数据;
  • 按大写/小写过滤数据; 筛选日期按年/月/日/周/季

DOC超filter1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


演示:根据您使用VBA代码输入的单元格值自动筛选行


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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 · 1 months 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 · 1 months 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 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 · 2 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 · 2 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 · 7 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 · 8 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 · 8 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 · 8 months ago
    The VB script worked beautifully. Many thanks for the post!
  • To post as a guest, your comment is unpublished.
    Bob · 8 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 · 8 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 · 9 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 · 9 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 · 11 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.