Як автоматично фільтрувати рядки на основі значення комірки в Excel?
Зазвичай функція фільтрації в Excel може допомогти нам відфільтрувати будь-які дані, як нам потрібно, але, іноді, я хотів би автоматично фільтрувати комірки на основі введення комірки вручну, що означає, що коли я ввожу критерії в комірку, дані можуть бути фільтрується автоматично відразу. Чи є якісь ідеї для вирішення цієї роботи в Excel?
Автофільтрувати рядки на основі значення комірки, яке ви ввели за допомогою коду VBA
Припустимо, у мене є такий діапазон даних, тепер, коли я ввожу критерії в комірки E1 та E2, я хочу, щоб дані автоматично фільтрувались, як показано на знімку екрана нижче:
1. Перейдіть до аркуша, в якому ви хочете автоматично фільтрувати дату на основі введеного значення комірки.
2. Клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню, що з’явиться Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наступний код у порожнє Модулі вікно, див. скріншот:
Код 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
примітки: У наведеному вище коді, A1: C20діапазон даних, який потрібно відфільтрувати, E2 - цільове значення, за яким потрібно фільтрувати, та E1: E2 це ваш критерій комірка буде відфільтрована на основі. Ви можете змінити їх відповідно до своїх потреб.
3. Тепер, коли ви вводите критерії в комірку E1 і E2 і натисніть вводити ключ, ваші дані будуть автоматично фільтруватися за значеннями комірок.
Фільтруйте дані за кількома критеріями або іншими конкретними умовами, наприклад, за довжиною тексту, з урахуванням регістру
Фільтруйте дані за кількома критеріями або іншими конкретними умовами, наприклад, за довжиною тексту, з урахуванням регістру тощо.
Kutools для ExcelАвтора Супер фільтр Функція - це потужна утиліта, ви можете застосувати цю функцію для завершення наступних операцій:
Фільтрувати дані з кількома критеріями; Фільтрувати дані за довжиною тексту;
Фільтрувати дані за великими / малими літерами; Фільтрувати дату за роком / місяцем / днем / тижнем / кварталом
Демо: автоматичне фільтрування рядків на основі значення комірки, яке ви ввели за допомогою коду VBA
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
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.
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 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, 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??
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")
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..=?
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
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)
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.
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.
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
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 ?
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.
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.
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
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
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.
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
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.
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
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!
