Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Confusion (no records found)
I am having soem problems figuring out how to tackle a problem. I have been
successful in [1] autofiltering a spreadsheet; [2] selecting the filtered data and [3] copying it to a new sheet where I further manipulate it. However, I have found that when my autofilter finds no appropriate data (no record between two dates) instead of getting zero records I instead get all of the data copied. Can someone shed light on this and point me in the proper direction? Here is part of my selection and copy process: dLrow = Cells(Rows.Count, "P").End(xlUp).Row 'Field:=16 is Column P(card expiration date), D(last name, C(first name) Selection.AutoFilter Field:=16, Criteria1:="=" & startFilter, Operator:=xlAnd _ , Criteria2:="<" & endFilter Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select Selection.Copy Workbooks.Add Range("A2").Select ActiveSheet.Paste |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Confusion (no records found)
If those values in column P are really dates...
This is from "Excel 2002 VBA Programmer's Reference" Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm Search for "Range.AutoFilter" and you'll see this note: Range.AutoFilter The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" So you may want (guessing because you didn't share what startfilter and endfilter were): Selection.AutoFilter Field:=16, _ Criteria1:="=" & format(startFilter, "mm/dd/yyyy") Operator:=xlAnd, _ Criteria2:="<" & format(endFilter, "mm/dd/yyyy") Bigfoot17 wrote: I am having soem problems figuring out how to tackle a problem. I have been successful in [1] autofiltering a spreadsheet; [2] selecting the filtered data and [3] copying it to a new sheet where I further manipulate it. However, I have found that when my autofilter finds no appropriate data (no record between two dates) instead of getting zero records I instead get all of the data copied. Can someone shed light on this and point me in the proper direction? Here is part of my selection and copy process: dLrow = Cells(Rows.Count, "P").End(xlUp).Row 'Field:=16 is Column P(card expiration date), D(last name, C(first name) Selection.AutoFilter Field:=16, Criteria1:="=" & startFilter, Operator:=xlAnd _ , Criteria2:="<" & endFilter Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select Selection.Copy Workbooks.Add Range("A2").Select ActiveSheet.Paste -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Confusion (no records found)
Your right I should have probably included what startFilter and endFilter
were, and now I can't as I am at a different location. However, I have set startFilter and endFilter as dates earlier (hmm... perhaps they are strings, I better check). Anyway I am successful in getting the filtered records that fall between my dates with "Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select" BUT my issue is that if there are not records between the dates then "Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select" gives me all of the records. Thank you for the time you took to research this. "Dave Peterson" wrote: If those values in column P are really dates... This is from "Excel 2002 VBA Programmer's Reference" Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm Search for "Range.AutoFilter" and you'll see this note: Range.AutoFilter The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" So you may want (guessing because you didn't share what startfilter and endfilter were): Selection.AutoFilter Field:=16, _ Criteria1:="=" & format(startFilter, "mm/dd/yyyy") Operator:=xlAnd, _ Criteria2:="<" & format(endFilter, "mm/dd/yyyy") Bigfoot17 wrote: I am having soem problems figuring out how to tackle a problem. I have been successful in [1] autofiltering a spreadsheet; [2] selecting the filtered data and [3] copying it to a new sheet where I further manipulate it. However, I have found that when my autofilter finds no appropriate data (no record between two dates) instead of getting zero records I instead get all of the data copied. Can someone shed light on this and point me in the proper direction? Here is part of my selection and copy process: dLrow = Cells(Rows.Count, "P").End(xlUp).Row 'Field:=16 is Column P(card expiration date), D(last name, C(first name) Selection.AutoFilter Field:=16, Criteria1:="=" & startFilter, Operator:=xlAnd _ , Criteria2:="<" & endFilter Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select Selection.Copy Workbooks.Add Range("A2").Select ActiveSheet.Paste -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Confusion (no records found)
I'm not sure what range you're filtering (Selection doesn't help if you don't
share what the selection is). But if you can pick out the last used row by column P (or anything column) and the last used column by using row 2 (some kind of titles in row 1??? and field headers in row 2), then maybe this will help. It filters and copy of the data rows (avoiding the field headers) to a new sheet in a new workbook: Option Explicit Sub testme() Dim wks As Worksheet Dim myRng As Range Dim VisRng As Range Dim LastRow As Long Dim LastCol As Long Dim StartFilter As Date Dim EndFilter As Date Dim NewWks As Worksheet Dim ColsToCopy As Variant Dim iCol As Long Dim DestCell As Range Set wks = Worksheets("sheet1") StartFilter = DateSerial(2009, 4, 25) EndFilter = DateSerial(2009, 5, 1) ColsToCopy = Array("D", "E", "P") With wks 'remove the existing filter .AutoFilterMode = False LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column If LastCol < .Range("P1").Column Then MsgBox "Not enough columns!" Exit Sub End If 'titles in Row 1. Field headers in row 2 Set myRng = .Range("a2", .Cells(LastRow, LastCol)) With myRng .AutoFilter Field:=16, _ Criteria1:="=" & Format(StartFilter, "mm/dd/yyyy"), _ Operator:=xlAnd, _ Criteria2:="<" & Format(EndFilter, "mm/dd/yyyy") If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then 'nothing visible Set VisRng = Nothing Else Set NewWks = Workbooks.Add(1).Worksheets(1) Set DestCell = NewWks.Range("a1") 'come down 1 row to avoid headers 'resize by -1 to avoid extra row at bottom Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) For iCol = LBound(ColsToCopy) To UBound(ColsToCopy) Intersect(VisRng, _ .Parent.Cells(1, ColsToCopy(iCol)).EntireColumn).Copy _ Destination:=DestCell Set DestCell = DestCell.Offset(0, 1) Next iCol End If End With 'remove the filter .AutoFilterMode = False End With With NewWks .UsedRange.Columns.AutoFit End With End Sub Bigfoot17 wrote: Your right I should have probably included what startFilter and endFilter were, and now I can't as I am at a different location. However, I have set startFilter and endFilter as dates earlier (hmm... perhaps they are strings, I better check). Anyway I am successful in getting the filtered records that fall between my dates with "Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select" BUT my issue is that if there are not records between the dates then "Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select" gives me all of the records. Thank you for the time you took to research this. "Dave Peterson" wrote: If those values in column P are really dates... This is from "Excel 2002 VBA Programmer's Reference" Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm Search for "Range.AutoFilter" and you'll see this note: Range.AutoFilter The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" So you may want (guessing because you didn't share what startfilter and endfilter were): Selection.AutoFilter Field:=16, _ Criteria1:="=" & format(startFilter, "mm/dd/yyyy") Operator:=xlAnd, _ Criteria2:="<" & format(endFilter, "mm/dd/yyyy") Bigfoot17 wrote: I am having soem problems figuring out how to tackle a problem. I have been successful in [1] autofiltering a spreadsheet; [2] selecting the filtered data and [3] copying it to a new sheet where I further manipulate it. However, I have found that when my autofilter finds no appropriate data (no record between two dates) instead of getting zero records I instead get all of the data copied. Can someone shed light on this and point me in the proper direction? Here is part of my selection and copy process: dLrow = Cells(Rows.Count, "P").End(xlUp).Row 'Field:=16 is Column P(card expiration date), D(last name, C(first name) Selection.AutoFilter Field:=16, Criteria1:="=" & startFilter, Operator:=xlAnd _ , Criteria2:="<" & endFilter Range("D3:D" & dLrow & ",E3:E" & dLrow & ",P3:P" & dLrow).Select Selection.Copy Workbooks.Add Range("A2").Select ActiveSheet.Paste -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel, How do I get AutoFilter to provide# of records found? | Excel Discussion (Misc queries) | |||
AutoFilter - How to determine number of records found | Excel Programming | |||
Counter for records found during Find | Excel Programming | |||
How to "Capture" Records Found Number During AutoFilter | Excel Programming | |||
how do I display filtered records found | Excel Discussion (Misc queries) |