Excel 2013 :: Retrieve AutoFilter Criteria VBA
Jul 20, 2014
Is it possible to retrieve an arrary of the autofilter criteria. I know this is possible in pre 2007 but with 2007 onwards I can't find a way of doing it.
I know I can get all the available items in a list by using the scripting.dictionary and also by using the visible cells I could see what could be filtered but that is not really accurate for what I want.
E.G.
If I have multiple columns and look at the filtered information in one column I can retrieve an array of the visible cells from that column but that is not necessarily the criteria that is in the column. i.E. If another column has a filter rows may be filtered that would have otherwise been visible.
View 5 Replies
ADVERTISEMENT
Nov 9, 2013
I am using Excel 2013. I have an autofilter turned on for the columns in the spreadsheet. When I click on the filter button at the top of a column, the filter values do not have any check boxes next to them so I can't check any values. how to fix it so that it has checkboxes? I've attached a screen shot.
View 2 Replies
View Related
Oct 23, 2013
I've a excel workbook (2013) with aprox. 10 sheets (will include more). I want a macro code which search with different criteria.
when I run that macro a popup box will appear with criteria option, when I fill the criteria macro search that in entire workbook and show the result.
View 5 Replies
View Related
Jul 30, 2014
I am trying to save filter options to so I can apply the same filter to multiple spreadsheets. For example, I have several spreadsheets with 50 or more school names and I am trying to set a filter that will filter out the same 20 schools each time. Is there anyway to do this in excel 2013?
View 4 Replies
View Related
Jul 11, 2013
2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
View 5 Replies
View Related
Apr 28, 2014
I'm having a hard time making this maro work in Excel 2010.
I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.
Below is the coding I have that Excel is not liking.
Sub FilterAccurateRawData()
'
' FilterAccurateRawData Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
Operator:=xlFilterValues
Sheets("Instructions").Select
Range("A9").Select
End Sub
View 3 Replies
View Related
Jul 27, 2007
I have a spreadsheet with columns A:AH. I need to be able to retrieve the last 50 entries based on a number from column M (Area). For example, I need a message box asking me what Area I want. (The choices are 1 to 7). When I select the Area number I need to retrieve columns G:J for the last 50 entries for that Area. In selecting the entries, it needs to ignore any rows where there is either a blank cell or a period in column I. This is a dynamis spreadsheet with rows added almost daily.
Zipped spreadsheet attached.
View 2 Replies
View Related
Oct 22, 2008
I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
View 6 Replies
View Related
Nov 29, 2005
I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series.
I've been trying to modify the formula to allow me to utilize one additional
criteria.
Original formula:
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
Here is my formula:
=SUMPRODUCT((($O$7:$O$2710<>"")*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&""))
I'm trying to say how many unique instances are there for column O, where
column m = "1986?"
View 10 Replies
View Related
May 24, 2008
I have multiple worksheets to add employee details using a form and create worksheets (copy templateSheet and paste) for each employee using their uniq user id, which works fine. I have 12 worksheets named by month (Jan, Feb, Mar.... Dec). I have only 2 worksheets Jan and Feb for test.
I want data from employee worksheet's (worksheets created by employee user id) to be in 'Jan, Feb... Dec' worksheets to use a criteria (userid, worksheet name) to get monthly holiday booked data from employee worksheet (userid and worksheets name are same) to month name worksheet. I can do it manually (Example: =rahmanm!C3
) for each row and column, but I will also have a function to delete employee name and details. Is there anyway in VBA I can update worksheets (Jan, Feb, Mar... Dec) from employee worksheets (rahmanm, hallD, aldridh.. ).
View 3 Replies
View Related
Jan 6, 2014
I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.
Formula Test.xlsx
View 3 Replies
View Related
Apr 15, 2014
Using EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
View 2 Replies
View Related
May 15, 2008
Say you have a long list of data, and you go to Data menu --> Filter --> AutoFilter
And then you want to use the Custom AutoFilter. Here's a screenshot:
[url]
Is there any way to do MORE than two autofilters?
View 12 Replies
View Related
Jul 23, 2009
Been looking at various options. Just want something to simply show the autofilter critera (1 & 2 or how every many there are) in a text box. The range being filtered is column A : M.
Basically taking this further, want to use the criteria of column C to lookup an email address e.g.
'C' is filtered by "Company 96"
Lookup "Company 96" from sheet2 range A:B, with column A containing name, and column B containing email address
View 9 Replies
View Related
Oct 27, 2012
I have a list of zip codes in excel, which I need to pass to store website and retrieve all the store locations in that area..
For example:
[URL] .... "choose my store on the left"
or [URL] ....
Is it possible to do this in Excel/ VBA?
View 3 Replies
View Related
May 30, 2014
I am tired of copy and paste from one excel workbook to other workbook. How can I set up an automatic retrieve from one excel workbook to other excel workbook?
View 1 Replies
View Related
Apr 7, 2013
I was curious if Excel was able to pull information off websites and automatically populate a table such as items from Safeway and a price then another column for Walmart with same item and price? I am betting not on its own but I figured this was the best place to ask since I am trying to find an easier way to find savings at the store.
View 2 Replies
View Related
Jul 18, 2013
I have used MS Office Excel 2003 for many years without difficulty in XP. Suddenly I find I can't open many of my old documents. I get a message,"Microsoft Office (InfoPath2003) cannot open (the following file, etc.) A second message says, "The form contains XML that is not parsed: An invalid character was found in the content. Line 0, Position 0" Then I get the message "MS Office is not currently set as default." I complete the instructions that follow and it is now corrected but I continue to get the same error messages.
View 2 Replies
View Related
Jun 3, 2006
I'm trying to retrive files from a given directory into excel.
I already have this file from another posted message:
Sub GetFileNames()
Dim F As Long
Dim FileName As String
Dim TheNames As Variant
ReDim TheNames(1 To 1)
FileName = Dir$("*.*")
Do While Len(FileName)
F = F + 1
ReDim Preserve TheNames(1 To F)
TheNames(F) = FileName
FileName = Dir$()
Loop
Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames)
End Sub
But I want to be able to set the path myself
View 11 Replies
View Related
Aug 7, 2014
My problem is following: I have a list of data that are classified according to a particular character, and I want to copy (with auto filter through the macro) the relevant information to the appropriate place in the sheet where it belongs. That's no problem. The problem is that if I want to copy data, classified by a character that is not listed in the table (that is not in the filtering criteria), then all the data are copied to the appropriate place. But I do not want to copy in this case nothing. How should such a macro look like?
Find attached an example : example.xlsm
View 3 Replies
View Related
Dec 4, 2011
This is the following code i have a problem with:
ActiveSheet.Range("$B:$J").AutoFilter Field:=1, Criteria1:=Sheets("Report").Cell(0, -2).Value
I am trying to filter items on a sheet called "all the answers" and i want to look up a value on the sheet called "Report" to filter.
The cell i want to filter is two cells to the left of the active cell on that sheet.
View 5 Replies
View Related
Oct 5, 2012
I have some data which I want to apply multiple criteria to for a particular column. Searched around on the internet and it would appear I should use an array and pass that to my criteria. What I can't find an answer for is how to say "does not equal any of the values within the array"
Code currently is:
Code:
Dim NumberFilter_Array(0 to 2)
NumberFilter_Array(0) = 2
NumberFilter_Array(1) = 9
[Code].....
I've tried variations for that in red but can't get it to work, how to correct the line in red to filter for none of the values in the array NumberFilter_Array?
View 2 Replies
View Related
Nov 1, 2012
I am trying to use VBA to set an autofilter that hides all zeros in Column AL and then excludes all values in Column E that start with "312" or "502". For some reason I can't get this to work as expected, it still continues to display unwanted values in Column E.
HTML Code:
ActiveSheet.Range("$A$1:$CS" & LastRow).AutoFilter Field:=5, Criteria1:=Array("312*", "502*")
ActiveSheet.Range("$A$1:$CS" & LastRow).AutoFilter Field:=38, Criteria1:=Array("0")
View 5 Replies
View Related
Apr 30, 2014
Is there a way to add a dynamic number of criteria(based on user input) to an autofilter.
Code:
ActiveSheet.Cells(1, 1).AutoFilter Field:=1, Criteria1:= dynamicfilters
View 4 Replies
View Related
Aug 18, 2008
I have one master sheet of data, with a large number of fields and data.
I need to turn this master data into individual records, each record exisiting as an individual worksheet - lets call it a 'U'. The U is a template sheet which has calculations and lookups built into it to complete further information. The completed U's are then used by a number of people for different reasons. There are 3 main 'flavours' of these sheets which have slightly different uses.
I've gone from knowing nothing about macros to having learned enough about them in the last week or so to populate each individual sheet with the data, and save the new file in the location I want it to go.
What I want to do now is filter the fields displayed by the individual U sheets, as not every field is applicable to each 'flavour'. I've marked up the rows as to the appropriate flavour - e.g. Row 17 is applicable to 'P' 'F' and 'R' ( Cell which is auto filtered contains PFR), but Row 18 is only 'P' and 'F' (Cell contains PF).
I've gone through the master file and identified each entry as a the appropriate flavour - to summarise what I'd like to do now:
1) Automatically populate the template file with the relevant data. (which my macro will do)
2) Use an autofilter to filter the rows equal to the data in the reference sheet so these are the only ones displayed. Eg. Reference sheet says 'P', so I want to filter the U sheet where autofilter column contains the letter 'P'
3) Rename the file and save as my reference in the location I want it to (which the macro is doing).
Here's what I've got:
Range("CF3").Select
Selection.Copy
Windows("USS iss1.xls").Activate
Range("G158").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell, Operator:=xlAnd
(where CF3 = the cell in the master data with the flavour in it, "USS iss1" is the template U file, G158 is a spare cell and Autofilter Field 1 contains the row reference which tells me which data applies to which flavours).
I've tried using the macro recorder, which when I paste the value in the autofilter/contains box records it as the value I've just put in rather than a copy of the reference cell. I've tried
Criteria1:=*ActiveCell*
and other variations on the same theme, but to no avail - I get "Compile error: Expected:expression".
View 3 Replies
View Related
Apr 16, 2006
You posted this code and it works well
could u advise how to display just the criteria ie no heading, no :, no =, and when it displays the criteria could it fill the cell with a colour. When filter is set to "all" give a blank cell
This would over come lots complaints from operators not realizing that filters are on because they can not find the silly blue button.
Are microsoft aware of is and are they changing it in the new release.
View 9 Replies
View Related
May 16, 2006
I'm actually trying to write a Macro that applies autofilters and the information just doesn't show! The autofilter is properly applied and everything, but the only visible row is the header. The code is the following.
mes = Month([M2])
filfecha = Range("M2").Text
Columns("E").AutoFit
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=filfecha
View 2 Replies
View Related
Mar 5, 2007
I would like to do some search function. As I know, to have "contains" in criteria, I can simply add *. But then, the asterisk(*) doesnt work with forms. I want my user to put the word they are looking for in a form then the system will select from a different worksheet. Below is my coding and bold is my biggest problem.
Sub Advanced_Find()
company = Workbooks("CONTRACT").Sheets("Advanced Find"). Cells(9, 3).Value
Workbooks("CONTRACT").Sheets("Sheet2").Activate
Range("C3"). AutoFilter Field:=3, Criteria1:=*company*, Operator:=xlAnd
Sheets("Sheet2").Rows(3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.WindowState = xlMinimized
Workbooks("CONTRACT").Sheets("Advanced Find").Activate
Workbooks("CONTRACT").Sheets("Advanced Find").Cells(15, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub()
View 2 Replies
View Related
Mar 16, 2007
I want to write a VBA routine to compare the Autofilter criteria found on two different worksheets. If the criteria is not the same I will display an error message. The range the Autofilter is working on could be one to many columns. The criteria could be anything from selecting a value from the autofilter list to multiple columns using a custom autofilter with multiple conditions.
View 2 Replies
View Related
Jun 22, 2007
I'm trying to make the criteria in an autofilter bit of code be the value of a cell in my spreadhseet. I have named the cell and would like to reference the named range rather than the cell address if possible.
Exp = Range("ExpenseGroup")
Sheets("Sheet2").Select
Selection.AutoFilter Field:=2, Criteria1:=Exp
Sheets("KPICharts").Select
Range("A1").Select
ExpenseGroup is my range which is located on sheet "cntrl" cell "G5"
When I run my code it referes to line 1 and says" Function call on lefthand side of assignment must return a Varient or Object".
View 8 Replies
View Related
