#1
|
|||
|
|||
Filter not returning all relevant results
I have a data set of ~250,000 rows and 8 columns. I have selected all the data and added a filter but when I filter by a certain word, it does not return all corresponding values.
Here is the data set: https://www.dropbox.com/s/dwu5u5vk8t...book.xlsx?dl=0 Here are the actual steps I took: Filter column B with the word "fitness" Remove it Sort column F in ascending order Filter column B with the word "fitness" An entire different data set appears. I know this because after I filtered it the first time, I applied the word "fitness" to everything in the industry column (less a few specific rows). When I re-sort it, it filters data that doesn't contain the word "fitness" in the industry column. Here's what I would like to happen: I would like it to display ALL of the data in Column B containing this particular word. Please help. |
#2
|
|||
|
|||
Can you show us exactly what you used for the filter.
I believe you have fitness in column B in 5554 records and in column I in 6373 records. Last edited by NoSparks; 07-10-2015 at 10:05 PM. Reason: added number of records |
#3
|
||||
|
||||
External links being potentially unsafe and not always reachable by all members, please post your sheet on the forum. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
HI,
If the word "fitness" is in column "B" and there are no "Blanks" in column "B". This code may work. Not sure if the size of your data will effect it. Also as mentioned supply a copy of your work. Code:
Sub Test_Filter() Dim i As Long Dim VisRng As Range Dim Myval As Long Dim lrow As Long '' set filter '' lrow = Sheets("Sheet1").Range("B65536").End(xlUp).Row '' rename to your sheet Sheets("Before").Range("A1:f1").Select Selection.AutoFilter With Selection .AutoFilter Field:=2, Criteria1:="fitness" '' this set the filtered data for the value End With With Worksheets("Before").AutoFilter.Range Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) '' make sure you have more than 1 row '' Myval = .Range("B2:B" & lrow).SpecialCells(xlCellTypeVisible).Count If Myval >= "2" Then Range(Cells(VisRng.Offset(, 0).Row, 1), Cells(Range("B65536").End(xlUp).Row, 8)).EntireRow.Delete End If End With Selection.AutoFilter End Sub |
Tags |
filter, filtering |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |
Linking between 2 relevant timelines | EC37 | Project | 11 | 06-06-2014 06:43 AM |
2013 search results take a long time - they fill in as results in reverse date order | themookman | Outlook | 0 | 10-11-2013 12:01 PM |
Scores returning ranked results | TerryStevenson | Excel | 1 | 09-16-2013 12:20 PM |
How to relevant task to a folder | yoni | Outlook | 1 | 01-02-2012 12:29 AM |