Filter By String Criteria

Jul 11, 2007

What i need to do is basically delete every row in my spreadsheet that contains the criteria "L-0.###" in said column. by ### i am referring to 3 decimals, such as:

L-0.250
L-0.000
L-0.555

and so on. you get the idea.

with the below program i got it working so i can type in:

L-0.***

and have it sort out everything proceeding L-0. ...problem is i need to keep the data such as this:

L-0.53
L-0.00
L-0.0

or basically everything not 3 decimals in with a head of L-0.

here is the macro i'm using:

Sub DeleteRowByString()
'
' DeleteRowByString Macro
' Macro recorded 7/10/2007 by Khenzel
'
' Keyboard Shortcut: Ctrl+Shift+S
'

'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that should be removed from matching columns. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="Ferguson Enterprises, Inc.", Type:=1 + 2)

'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub

'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found. (Ex. A=1, B=2, Etc.)", Title:="Ferguson Enterprises, Inc.", Type:=1)

if possibly a modification in my coding to make this work, or simply a criteria i can type in to make it happen.

View 4 Replies


ADVERTISEMENT

Advanced Filter Code - Criteria Range More Than 1 Row Breaks Filter

May 1, 2014

I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.

But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.

I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.

Here's the code :

[Code] .....

Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...

View 4 Replies View Related

Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

View 1 Replies View Related

Filter For String In Multiple Columns

Jun 22, 2013

I am staffing an upcoming event with volunteers and I want to be able to a single volunteer across their various shifts and locations. However, I cannot guarantee that a particular volunteer will always be in the same column on my spreadsheet so AutoFilter doesn't do the trick. I've tried using Advanced Filter options as well, but I either can't seem to get it to work or it won't work with strings.

I've attached a sample of the type of document I'm working in (genericized) so hopefully my below questions will make more sense.

Ideally, I'd like to be able to be able to search/filter for Joe and see rows 2 and 3 appear. Is this possible?

Additionally, I'd like to know who is also on shift together. For example, is there a way to search for Tony and Sarah together and have row 5 appear. I know that I can do this now with filtering column 2 and then 3, but the real document I'm using is over 800 rows long (very big event!) and using progressive filtering would take longer than I'd like.

View 4 Replies View Related

Filter Columns With Specific String?

May 28, 2012

I want to filter columns from an excel spreadsheet where the value in the cells contains/includes the String "Create".

View 3 Replies View Related

String Search - To Create A Combobox To Filter A Set Of Data By The Month That Is Entered

Jun 7, 2009

I am trying to create a combobox to filter a set of data by the month that is entered.

The below code worked fine when there was just the month entered, but now all the entries in the sheet are in the format 01 January 2009. So I need a section of code which will search for the combobox value as part of a string in my range.

The cbodate values are Jan, Feb, March etc ....

View 9 Replies View Related

Filter The Two Criteria

Dec 12, 2009

I have a filter and need 2 (two) criteria.
1) In a (long) list of dates I need to see only the last month
2) From that selection (last month) I need to filter a date.

Note: I would like the filter to only show the last month as the list becomes very long.

Current solution
Column A has all the dates
Column B is a copy of column A
I use a small macro

View 6 Replies View Related

Filter With More Than 3 Criteria?

Dec 13, 2011

Currently I got 1 vba coding which only filter with 3 criteria. Can I alter the vba to have 4 to 5 criteria in the filtering? Below is my VBA coding. Let say I want to add 1 more filter for column C2...

Range("A2").Select
Selection.Sort key1:=Range("E2"), Order1:=xlAscending, key2:=Range("B2"), Order2:=xlAscending, key3:=Range("A2"), Order3:=xlDescending, Header:=xlYes, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

View 3 Replies View Related

Filter Out By Criteria

Jun 7, 2007

I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc.

I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet.

View 9 Replies View Related

Filter Based On 2 Criteria

Oct 29, 2013

In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .

View 6 Replies View Related

Filter Criteria By Cell

Jul 10, 2012

How to give a cell link instead of any value for filter criteria, For example I need the data between two date range, the first date is in cell A1 and second date is in cell B1 by using macro. I have tried by using below code also but after filter all data hiding.

Dim a As Date
Dim b As Date
a = Range("a1")
b = Range("b1")
Rows("2:2").Select
Range("A2").Select
Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:=">=a", _
Operator:=xlAnd, Criteria2:="

View 1 Replies View Related

Filter Using Multiple Criteria

Nov 18, 2013

I have a Excel sheet which has a Product type values ASP,VSP etc in the first column. The second column has values Product Sub Types,which has values abc123, abc123456,abc123789, def123,def123456,def123789.The third column ServId's abc01,abc02,def01,def02.

If the Product Type is ASP,then I need values abc123,def123

But if Product Type is VSP, then I need values abc123
abc123456;abc123789
def123
def123456;def123789

View 4 Replies View Related

Filter On Multiple Criteria Using VBA

Jun 13, 2007

I have a dataset, which I would like to sort on multiple criteria. The code I have is :

Dim Lst As Long
Dim Hdr As Range

Lst = Range("A65536").End(xlUp).Row
Set Hdr = Range("A6:AD" & Lst)

With Hdr
.AutoFilter
.AutoFilter Field:=9, Criteria1:="Complete" 'Match Status
.AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status
End With

Field 9 Match Status will always be Criteria "Complete"
Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.

This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".

View 9 Replies View Related

Filter A Row In Specific Criteria

May 5, 2009

I would like to filter a row using the following criteria: date should be < or = todays date.

I have tried the below but it doesn't work

Selection.AutoFilter Field:=11, Criteria1:="

View 9 Replies View Related

Use Cells For Filter Criteria

Aug 20, 2008

I am using the code below to coax Autofilter into letting me use three criteria. I can hard code the values in for two of the cells (B6 and C6 in this case) but for it to be of any use I need the array to use the actual values found in those two cells.

With Worksheets("Sheet2")
Cells.Autofilter field:=1, Criteria1:=Sheets("Sheet1").Range("B1").Value
Cells.Autofilter field:=6, Criteria1:=Array("2880", "2879"), Operator:=xlFilterValues, _
Operator:=xlOr, Criteria2:=Sheets("Sheet1").Range("D6").Value
End With

How should this be setup to allow the array to read the two actual cell values?

View 2 Replies View Related

Filter With Cell Value As Criteria

Nov 8, 2006

I have a table that I filter out each buyer

Buyer Item Weight

Smith PartA 1000
Smith PartB 1000
Jones PartA 1000
Jones PartB 1000
Fred PartC 900

So when I filter out Jones, it would look like this

Buyer Item Weight
Jones PartA 1000
Jones PartB 1000

With Jones cell reference A4 & A5. Can I link a cell to the 1st visible cell below the autofilter? This cell would change each time I selected a different buyer.

View 3 Replies View Related

Filter By Date & Other Criteria

Oct 24, 2007

Anyway, in my workbook I have a master sheet which contains all the information entered (this master sheet will continue to grow as more information as entered). I want to create a userform to allow someone to search the data by selecting a date range (from/to) and a criteria (Pending/Settled) and for the results to be copied into a new sheet (simply I don't want the raw information tampered with in an unregulated way).

View 3 Replies View Related

Filter By Criteria To Another Range

Jun 1, 2008

i want to display the results of one criteria using VBA codes instead of just filtering one by one to display the result and collate it. I have attached the sample. I want to find the "subcodes" of name "kathy". to be able to do that, i filter "kathy" to display all the "codes" then filter all the "codes" to find the "subcodes". is there vba code for this one so that when i input the name, all the subcodes will be displayed?

View 6 Replies View Related

Filter By Multiple Criteria

Jun 18, 2008

I am trying to run a macro to filter data based on 3 criteria. i want to filter column c to show rows with "Application", "Application / Web", and "Application / Database" which is referenced in the criteria range stated below.... Sheets(" Validation").Range("D95:D97"). for some reason it does'nt pick up the answers when i know that they exist in column c. could it be that they are formulas populating column c?

Sub Macro3()
Range("c4:c200").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Validation").Range("D95:D97")
End Sub

View 3 Replies View Related

How To Filter Information Based On Two Criteria

Apr 29, 2014

I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:

I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.

How to use match and index function or any other function to fetch information against this date as per the agent selected.

View 3 Replies View Related

Macro To Filter On Multiple Criteria?

Apr 21, 2014

i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21

Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.

for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.

in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.

View 14 Replies View Related

Advanced Filter With Multiple Criteria?

Mar 5, 2014

Can I use vba advancedfilter to work with more than one criteria?

I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:

[Code] ......

I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...

The months are numerical in those fields - 1 through 12.

Can I add to the present filtering line or do I need to then create an additional filter?

View 6 Replies View Related

Multiple Criteria Using Auto Filter

Oct 21, 2008

I have a range of data (up to 20,000 cells) that has up to 30 different entries. I need to apply a filter to this data to filter out all but 5 of these.

View 5 Replies View Related

Use Cell Data For Filter Criteria

Feb 15, 2010

I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.

View 9 Replies View Related

Formula To Find The Filter Criteria

Dec 10, 2005

I have a worksheet which has in column A2:A9 a label and in column
B2:B9 a value, like so:

A1="LABEL" B1="VALUE"
A2="LabelGreen" B2=452.47
A3="LabelBlue" B3=-87
A4="LabelRed" B4=9
A5="LabelGreen" B5=-7
A6="LabelBlue" B6=1888.97
A7="LabelRed" B7=144
A8="LabelGreen" B8=-0.02
A9="LabelBlue" B9=87002

I use the autofilter on column A to select only certain labels.

in cell D1 I use the subtotal formula to give me the sum of all
filtered values.
D1=SUBTOTAL(9,B2:B9)

This works fine, for example, if I filter on column A for "LabelGreen",
the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45.

What I am trying to do now and what I have not yet found a solution for
is:

In cell C1 I want to display the criteria I have filtered for.
For example, when I filter column A for "LabelGreen", I want
"LabelGreen" to be displayed in cell C1.

I was thinking something similar to SUBTOTAL might do the trick, for
example:
C1=SUBFIRST(9,A2:A9)

But this only gives me (and I am not surprised) an error "#NAME?"

View 12 Replies View Related

Advanced Filter List Criteria

Oct 25, 2011

I'm trying to use an advanced filter to filter a large data set using several parameters. I had this worked out before but there have been some parameters added that have thrown my filter off balance and I'm struggling getting it back.

Here's the criteria I need to filter

Column 1:
85

Column 2:
3

Column 3:
BU 1
BU 2
BU 3
BU 4

Column 4:
BA 1
BA 2
BA 3
BA 4
BA 5
BA 6
BA 7

Since I have an uneven list of criteria, I'm struggling to figure out the layout with the 'AND' condition. I know I can copy the 85 and 3 figures to each line but when I have 4 figures in column 3 and 7 figures in column 4, how can I create the 'AND' condition for all these criteria?

View 2 Replies View Related

Multiple Advanced Filter - 3 Criteria

Feb 7, 2012

I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:

Col A Col B
Name Animals
Smith Cows, Pigs, Sheep, Horses
Jones Sheep, Pigs, Chickens, Geese, Alpaccas
Price Cows, Sheep, Pigs, Chickens, Rabbits
Williams Cows, Chickens, Horses, Alpaccas, Pigs

I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.

The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.

View 2 Replies View Related

Advanced Filter With Double (or / Or) Criteria

Jan 11, 2013

as u can see on picture I have some data and i want it to filter with "debet" and "credit",while debet can be zero or number x when in the same time credit can be zero or the same number x so when entering number x meaning e.g. 500 i get to rows

first
debet = 0 and credit = 500

second
debet = 500 and credit = 0

while running advanced filter i was getting nothing because i couldn't write what i needed

View 5 Replies View Related

VBA Advanced Filter Criteria Range

Jun 12, 2008

When doing advanced filter in VBA, is there a way to set the criteriarange, without having actual cells on a worksheet with the criteria in?

I've tried criteriarange:=Array("Currency", ws.Name), but it didn't like it.

View 9 Replies View Related

Filter For CASH NOMS And Keep Everything Thats In The Criteria

Sep 24, 2009

i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms

criteria is named range "Criteria" (I5:K10)

So from this

Sheet1  ABCDEFGHIJKL4        Criteria   5RecTeamAmountRef    RecTeamRef 6CASH NOMSAMP1132.36CAETRN    CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3      *WTRN96 8CASH NOMSINTEREST915.12WTRN96      *AMPTRH 9CASH NOMSFA5-65.36AMPTRH      *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG      *WTWHAT 11CASH NOMSCA-9.01CBWSSG        12CASH NOMSDES-9.01MSFAST        13CASH LIFEUUTU-9.01CBWSSG        14CASH LIFEAMP1-9.01CBWSSG        15CASH LIFEAMP232.12.WSTFGH        16CASH LIFEAMP315975.36CBWSSG        17CASH LIFEAMP4-9.01OPTMOD        18CASH NOMSFA1-9.01CASH WTWHAT         Excel tables to the web >> Excel Jeanie HTML 4 .........................

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved