The range element below needs to be dynamic. A1 and down meaning if i was to do this out of VBA I would hold CTRL+SHIFT+DOWN but i cant do that from macro record as it doesn't work, I'm guessing this Range("A1:A73") is the element I need to change but I don't know what to?
Range("Main[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Input").Range("A1:A73"), Unique:=True
I am trying to create a function using vba and the advanced filter function.
The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.
This is my code.
Sub FilterCGML()
Dim rngToFilter As Range
Dim rngFilterCriteria As Range
Dim LastRow As Long, LastCol As Integer
With Sheets("Main FTE")
Range("b2000").Value = "Cost Code"
Range("b2001").Value = "5*"
It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.
I know how to find the last column and last row by using
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column
But I don't know how to modify my code to use these.
I have a spreadsheet with a large number of graphs on and lots of data.
Most the graphs look across 20 or so columns and one or 2 rows down.
The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.
Therefore l believe using named ranges may solve this problem...
I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))
However i need to expand on this to achieve what i need to do.
1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)
2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.
3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?
I need to filter data which is 3 months from todays date or older. I'm going to use it in a macro which downloads the information from our accounts system.
I have set up the filter and it works fine when I manually put the Date in, but when I use the date 'formula' for todays date -3 months and reference the cell its in, it does not work. Here's what I have so far :
The filter from the macro :
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("J1:J2"), Unique:=False
The way I work out the date 3 months from now (in cell K2) :
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
Cell J1 has the relevent Column Header
Cell J2 has the following formula :
=" < K2 " (there are no spaces I had to put them in so the post appeared correctly!)
I am writing a VBA code to make an advanced filter. Instead of a fixed criteriarange, I would like it to be dynamic. As each time the number of criteria is different.
e.g.
Range("A1:G30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("L1:L18"), Unique:=False
I want the criteriarange to change. e.g. It include all the data if I put in 20 data in col L.
attached is a spreadsheet effectively replicating a criteria box I’m using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What I’m trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.
Below is the code I’ve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, I’m hoping the macro would only use those 2 rows instead of picking up all 7 rows.
See criteria box on spreadsheet for example. In this particular example, I’m wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with “1”s. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?
Sub RunDynamicSelection_Click()
Dim wsSheetDS As Worksheet
Dim wsSheetRS
Set wsSheetDS = Worksheets("DataSheet")
Set wsSheetRS = Worksheets("ReportSelection")
With wsSheetDS
.AutoFilterMode = False
With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _
.UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................
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...
Can anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies View RelatedHow do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
I have a large Excel with details of 1,000 staff
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James
Wilkonson, Paul,
etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
View 8 Replies View RelatedI have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
why the Advanced Filter works in the macro but not in the UDF?
Code:
Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name
Dim c As Range
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
[Code]....
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
I have a huge list (over 900,000 rows) of text codes (e.g. ABC-12345) on Column A Spreadsheet 1 and would like to remove the ones that are not included in Column A Spreadsheet 2.
I changed all of the text on Column A Spreadsheet 2 to have the in front of it (e.g. ABC-1234) and tried to run the advanced filter but it does nothing.
This code works, but I would like to cut (remove) the records from the original record list.
Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("H1:H3"), CopyToRange:=Range("J1:O1"), Unique:=True
I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.
resource1resource2resource3 XResources YResources
Jeff X resource2
John X X resource2 resource3
Jim Y resource1
I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.
i am trying to work out the coinage for wages ie. 5546.75 needs to brake down into dominations ,notes and change i live in south africa so we use the desimal system. i have tried to use floor and mod as formulas but at some points it returns an error or incorect result.
View 8 Replies View RelatedI am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
View 9 Replies View RelatedI want to create an advanced filter criteria along the lines of
=AND(col1>begindate,col1<endate)
where 'col1' is the title in the cells at the head of the appropriate columns in the data range (B1), criteria range(F1) and extract area range (I1), and 'begindate' and 'enddate' are named single cells.
Why doesnt this work?
i am trying to use the autofilter command to paste 4 columns else where on the page using unique = true. this only will run once. when i try to run it again everything is blank. please see code below.
Sub copyindex01()
Columns("L:O").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
Range("G2").Select
Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"M:P"), Unique:=True
Range("L1").Select
End Sub
I created an advanced filter macro which analyzes the "Orig_Appmt_Data" dataset from the "Summary" worksheet and provides the resulting analysis on the "Effective Rate" worksheet. However, when I change the name of the workbook, the macro fails. I was hoping someone could provide some assistance in helping adjust the vba code to take into account the subsequent changes in workbook filename. Here is the
Sub ER_Analysis()
'
Range("B8:R109").Select
Selection.ClearContents
Columns("P:R").Select
Selection.EntireColumn.Hidden = False
Range("Q5").Select
Range(" Conversion 3-28-07.xls'!Orig_Appmt_Data"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Q5:Q6"), _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R8").Select
Selection.AutoFilter
Range("B8:R87").Sort Key1:=Range("P8"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:="<>", Criteria2:="<> TOTAL"
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.Font.Bold = True
Range("P8").Select
End Sub
I am trying to create my critera section for an Advanced Filter, but it seems like Auto Filter uses each row as a specific filter.
i.e. If I need to filter all the "2"s in column A, but both the "1"s and "2"s in column B, I need to create two different filters like this:
Column A Column B
2 1
2 2
Instead of just:
Column A Column B
2 1
2
Is there any way I can use auto-filter without having to put in every possible combination of my criteria in as different filters? Auto Merged Post;I did try creating columns next to each other, but that seems to only work as an "AND."
Per my example, I need to return everything with column A = 2, and column B = 1 or 2.
Except in my actual spreadsheet, it's more like where A = {1,2,3,4,5} and B = {0,9,8,7,6} and C = {a,b,c,d,e}
I've written a macro in an attempt to automate the advanced filtering in Excel.
VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....
I have a large spreadsheet of data, with various columns with different bank names, the amount (column H) each bank holds plus loads of other columns. Some of the banks are duplicated, so in a way I want to do an advanced filter to get rid off the duplicate lines, except that I need the amounts (column H) summed.
View 2 Replies View Relatedi have a lot of data which i am going to be running an advanced filter on. One of the criteria i am trying to filter for is a list of text values that i want to make sure a row does not contain. Lets say for example I have a list of 2 text values in column "symbols" that i would like to omit in my filtered results, "abc" and "xyz".
As of now I can only set up a criteria to omit one of those.
Symbol
<>abc
this works right now.
But if i try to put <>xyz in the cell immediately under there, I thought its supposed to be filtering for symbol that does not equal abc or xyz, but instead it doesnt filter anything out. I think it may be a syntax error or something else that I am doing wrong. Can anyone point me in the right direction?
Ideally, I would like to be able to filter out a list of text values that I keep in another column. Like lets say today I wanted to omit "abc" "xyz" and "lmnop" and I would have the advanced filter criteria just reference that list of values as what I would like to filter out. Except there would probably be a list of at least 20 to 30 values, that change over time, so it would be much easier to just keep it all neat in one column that I can edit as I need to.