How Filter Macros Work

This topic on Ozgrid provides just what I need, but limited to only 2 criteria - my VBA is not up to extending the code to the ten criteria that I need. Can anyone point me in the right direction please? VB:

Function AutoFilter_Criteria(Header As Range) As String  Dim strCri1 As String, strCri2 As String        Application.Volatile            With Header.Parent.AutoFilter          With .Filters(Header.Column - .Range.Column + 1)                        If Not .On Then Exit Function                                strCri1 = .Criteria1              If .Operator = xlAnd Then                  strCri2 = " AND " & .Criteria2              ElseIf .Operator = xlOr Then                  strCri2 = " OR " & .Criteria2              End If                        End With      End With                AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2  End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I am trying to use VBA code to filter data. The filter criteria is based on the cell values in another worksheet. The code works when my criteria only includes less than and greater than but when i try to include 'is equal to' or greater than the code doesn't filter like I need it to.

Here's the code:
VB:

ActiveSheet.AutoFilterMode = False 
Range("A1:X1").AutoFilter 
Range("A1:x1").AutoFilter field:=1, Criteria1:=ThisWorkbook.Worksheets("CONWAY").Range("B1").Value, _ 
Operator:=xlAnd 
Range("A1:D1").AutoFilter field:=2, Criteria1:="=" & ">" & ThisWorkbook.Worksheets("CONWAY").Range("C1").Value 'greater than filter to Contract ("B2" as criteria)
Range("A1:x1").AutoFilter field:=3, Criteria1:="=" & "<" & ThisWorkbook.Worksheets("CONWAY").Range("C1").Value 'less than filter to Op Code ("C2" as criteria)

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Excel 2007 compatability mode for 2003- I have a list tab with text values in column C "ID" and a tab with a pivot table. The pivot table had 500,000 rows of underlying data. Based on the cell values of "ID" in column C(in each row), I would like to be able to see the underlying data of the pivot table for only the rows of underlying data where the uderlying data field "ID" matches the "ID" value in column C.

Note that the pivot table can not use the field "ID" becuase there are too many values and it can not represent those.

Also note that I need to be backward compatable to 2003.

I tried a hyperlink but am not sure how to reference the underlying data or how to programatically spawn the results.

Hi,

I am trying to use a custom filter in say Column A. I have a number that begins with 5 but may have any number of digits after that. I tried using a wildcard after the 5 but it does not return any results. I tried using Text to Columns but that didn't help either. What would be the root cause that it would not work. I have several bits of "trial" information for example 51 555 5500 5000 and nothing seems to work when I put even "contains" 5* or "begins with" 5* or equals 5* none of those work. Can anyone help me out. Thanks for your help in advance!

Hello All,

First time poster. I have a large Excel Spreadsheet that has 32 columns and over 50,000 lines of data. Column U has a 4 digit code. There are over 100 different codes in column U. I want Excel to create a new worksheet where each code is only listed once but the contents are summated. Might be easier to show an example.

Here is how the data appears now:

Column U Column X

AAAA $10
AAAA $20
AAAA $50
AAAA $10
AAAA $100
BBBB $20
BBBB $10
BBBB $50
CCCC $80
CCCC $90

Here is the format I want excel to create:

AAAA $190
BBBB $80
CCCC $170

Thanks, Bill

I have the following code that copies only the visible cells in an auto filter...how could I modify this code to paste only the values and not the format?

Code:

.Range("a:a").AutoFilter Field:=1, Criteria1:="True"
    .Range("b:d,g:r").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Monthly Hours").Range("A1")

Hi,

I'm looking to make a macro that will search column A for a list of values and will return a Msgbox that lists any values that are found. If no values are found I want to show a Msgbox that no values were found.

Example:

Column A generally has 300-500 rows of ticker symbols (e.g. - CNSU , GOOG , APPL , MSFT , NFLX, MET , LXCC , BFN)

I have a list of symbols and I want to see if any of them are in Column A (VNF , MBLF, PNP , LXCC , DRAQ)


Important to note is that the list I am checking may also be up to hundreds of symbols, so I need a solution that is as efficient as possible.


I've found code elsewhere on the internet that does essentially what I want but can only search for 1 symbol. I won't post it as it's probably simpler to start from scratch.


Also, I realize that keeping an excel file list of the tickers I am checking for and then using VLOOKUP would probably be easier, however, I may not have that option at this point.

Thanks!

Hi,

I have a query with Pivot Tables. When I apply an autofilter on a Pivot Table it doesn't show the columns with all zero values, how do I get the table to show these columns?

My motivation for showing these columns with zero values is that I need to calculate some metrics (eg. top left cell of one pivot table divided by top left cell of another pivot table, in %) and so need the pivot tables to be of the same dimension.

Any help on this matter would be much appreciated.

Cheers

Hey All,

I have a macro that creates sheets and copies the relevant results from a Data sheet that is autofiltered based on three criteria. The problem I am having is when there is only one result from the filter it copies the line below it and I have a feeling it is due to the offset or row count? I should mention that it works perfectly for 2 or more results. Here is the snippet of code that is causing my headaches!

VB:

 'Begins Title filter and selection
FilterRange.AutoFilter Field:=1, Criteria1:="" & cboRepMonth.Value & "" 
 
FilterRange.AutoFilter Field:=2, Criteria1:="" & LoopStudio.Value & "" 
 
FilterRange.AutoFilter Field:=4, Criteria1:="" & cboRepYear.Value & "" 
 
ActiveWorkbook.Sheets("Data").Activate 
 
 'Copies visible entries to the Studio Tab
If ActiveSheet.FilterMode = True Then 
    ActiveSheet.Range("O1").Select 
    Do 
        ActiveCell.Offset(1, 0).Select 
    Loop Until ActiveCell.Rows.Hidden = False 
End If 
If ActiveCell.Offset(1, 0).Rows.Hidden = True Then 
    ActiveCell.Offset(1, 0).Select 
    Selection.Copy Destination:=ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 3) 
    ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 2).Formula = "Titles" 
    ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 2).Font.Underline = xlUnderlineStyleSingle 
Else 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy Destination:=ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 3) 
    ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 2).Formula = "Titles" 
    ActiveWorkbook.Sheets(Studio).Range("B" & LastRow + 2).Font.Underline = xlUnderlineStyleSingle 
End If 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The data is autofiltered based on the criteria input from a user form. Any assistance would be greatly appreciated. Thanks!

I'm trying to create a report to display the stats for a small team. If possible I'd like it to be completely automated- just generate the source data, open the calc spreadsheet, and hit update. Here's what I have to work with:

spreadsheet1
Column A has 6 digit account numbers in text format
Column M has dates the team member contacted the account
Column AE lists results A, B, C, or D
I output the above spreadsheet once per week for each team member

spreadsheet2
Column A has 6 digit account numbers in text format
Column B has order dates

Here's what I want to be able to report for each member of the team:
1 - how many accounts were contacted for the given week
2-5 - how many of each result the team member got, A, B, C, and D
6 - of the accounts contacted that week, how many of them placed an order that week
7 - how many orders have been placed by all contacted accounts project-to-date, not just that week
8 - how many of the accounts that have been contacted have since placed an order on 3+ separate weeks

Once an account qualifies for #13, they are considered 'complete' and no longer need to be contacted.

I can do it now but it's very clunky. The biggest roadblock to the way I wanted to do it is that while the account numbers are pre-existing, I don't know which team member is contacting what account numbers until they contact them, so I have to create/update the list of accounts for each team member every week. My current process is to run the first report, generate spreadsheet1, filter unique/copy/paste the account numbers to get a contacted list for each team member, run the second report with those numbers, output spreadsheet2, open the calc sheet to autogenerate the weekly stats, then copy/paste them into another tracker sheet. Then I do all that over again to get the project-to-date numbers. I only recently realized that team members could artificially inflate their numbers for point 6 by doing a followup call to an account that's already 'complete' and is going to order anyway, so I'm not sure how I can filter those out without a bunch of extra steps each week.

Any ideas on how to do all this automagically? I can output the first spreadsheet for the whole team and pick out each one's activity or do separate outputs for each team member. Same with the second report, once I have the new/updated list of account numbers for each team member from the first report.

idk