limited filter criteria

[dalerguy]

Hi,

I noticed that when using autofilter you can only use 2 criteria with a statement such as

copyRng.Autofilter Field:=7, Criteria1:="Wrong Num", Operator:=xlOr, Criteria2:="Disconnect"

I have a column (Field7) which I'm organizing the data by 5 criteria. Basically if you have value 1 or 2 or 4 copy to sheet 1, if you have value 3 copy to sheet2, if you have value 5 copy to sheet 3.

How can I use autofilter with 5 criteria?

Thanks

 

[dalerguy]

Here is the original sheet. I'm using column I (code) to filter groups of data to be pasted on different sheets alumni_records and supervisor_review. Hope this helps.

THanks!

Original Data

12-15-2005.xls
	A	B	C	D	E	F	G	H	I	J
1	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
2	B00001	Smith	John						Already Pl	1
3	B00002	MacDonald	Jim						Disconnect	2
4	B00003	Doe	John						Already Pl	3
5	B00004	Doe	Jane						Deceased	2
6	B00005	Smith	Paul				His son has applied to Medicine, and if he gets admission, then Dr. will send his donation, otherwise not.		Wrong Num	2
12-15-2005

Result of the current code I have used --
alumni_records sheet:

12-15-2005.xls
	A	B	C	D	E	F	G	H	I	J
1
2	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
3	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
4	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
5	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
6	PROSPECT ID	PROSPECT LAST NAME	PROSPECT FIRST NAME	ST COM CODE	STANDARD COMMENTS	FREE COMMENTS	EXTENDED COMMENTS	OTHER	CODE	CALLER
alumni_records

supervisor_review sheet:

[HTML removed by Von Pookie]

 

[dalerguy]

Sorry about the &nbsp... that was my first time using HTML maker

Mike

 

[Von Pookie]

That's ok, I've seen worse. I went ahead and fixed it so the nbsp's are gone, though--I had to go in and remove the last example, anyway.

Let's see, now.

Well, using the setup of your sample data, I had to change the autofilter to field 9 (column I) instead of 7 (column G).

But your problem with the copying simply comes down to how you're setting the data range. You are using Range("M65536").end(xlup) -- if there is no data in that column, it will stop at row 1, which you don't want.

Assuming the setup you have posted here is actually what you're working with, your last column is column J--not M.

So along with changing the autofilter field to 9, change the M65536 to J65536 and give it another go.

 

[dalerguy]

OMG! That is awesome!

It works fine and I truely appreciate your help. I have been trying to learn as much as possible from this BB and once again I have something to add to my notes

Thanks again

Merry Christmas!

 

[dalerguy]

I have found a new problem. Seems this function isn't grabbing all the data that falls within the criteria. For example,

There are 177 DoNot Call records, but only 129 have made it to the appropriate sheet. Also this it wasn't picking up Wrong Num records. The original data sheet has a sort on column I which is the filter column. When I sort asending Wrong Num code is at bottom. During the run it will ignore all Wrong Num records. However, when I sort descending wrong nums are included but Already Pl are short 9 records. Seems whatever code is at the bottom of the file is ignored or only part of the data is copied.

Any thoughts?

All my code for this macro
###################################
Public Sub import_Click()
' Open Text file, import to Excel
Dim myPath As String, myDate As String, savePath As String

' Set file location and file name
myDate = InputBox("Please enter filename: (mm-dd-yyyy.txt)", "Enter filename", Format(Date, "mm-dd-yyyy"))

'testing path
myPath = "C:\Documents and Settings\user\Desktop\Phonathon Comments\macro for chris\orig\"
savePath = "C:\Documents and Settings\user\Desktop\Phonathon Comments\macro for chris\new\"

'open tab delimited file
Workbooks.OpenText myPath & myDate & ".txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1)), TrailingMinusNumbers:=True

'autofit all rows
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select


'hide the user form
runfrm.Hide

'process file
'===================================================================

'format sheet 1
Call Sort
Call Rowcount
Call ColumnFormat

Call Addsheet
Call test


'save workbook after all processing is complete
ActiveWorkbook.SaveAs savePath & myDate & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

'show the user form
runfrm.Show

End Sub

Private Sub Sort()

'sort by Comment Code Column I
Cells.Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


End Sub


'set col width and text wrap on G, H, K, & L
Private Sub ColumnFormat()

ActiveWindow.SmallScroll ToRight:=6
Range("G:G,H:H").Select
Range("H1").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("G:G,H:H,K:K,L:L").Select
Range("L1").Activate
Selection.ColumnWidth = 50
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

'set column heading on Sheet 1
Range("H1").Select
ActiveCell.FormulaR1C1 = "OTHER"
Range("I1").Select
ActiveCell.FormulaR1C1 = "CODE"
Range("J1").Select
ActiveCell.FormulaR1C1 = "CALLER"
Range("A1").Select


End Sub

Private Sub Addsheet()

'add sheets to file for split function

Sheets.Add Type:=xlWorksheet, After:=Sheets(1)
Sheets.Add Type:=xlWorksheet, After:=Sheets(2)
Sheets.Add Type:=xlWorksheet, After:=Sheets(3)



'rename sheets
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "alumni_records"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "supervisor_review"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "other"

'make sheet 1 active for filter to begin
Sheets(1).Select


End Sub

Private Sub Rowcount()

'count rows before processing file, display in messagebox

Dim Rng As Range
Set Rng = Range("A2:A" & Range("A65536").End(xlUp).Row)
MsgBox WorksheetFunction.CountA(Rng) & " Records to process!"

End Sub

Sub test()
Dim DataRng As Range, PasteSheet As Worksheet
Dim myCriteria As Variant, i As Integer

'range of data--not including header row (row 1)
Set DataRng = Range("A2", Range("L65536").End(xlUp))

'an array that holds the terms that will be filtered for
myCriteria = Array("Deceased", "Disconnect", _
"Wrong Num", "Remove Lst", "DoNot Call", "No English", _
"Out Cntry", "Already Pl", "Yes Pledge", "Maybe Pledge", "No Pledge", "Spec Pldg", _
"Unsp Pldg")

Application.ScreenUpdating = False
With Rows(1)
.AutoFilter 'turn on autofilter on row 1
For i = LBound(myCriteria) To UBound(myCriteria)
.AutoFilter field:=9, Criteria1:=myCriteria(i) 'filter column I
Select Case myCriteria(i)
Case Is = "Deceased", "Disconnect", _
"Remove Lst", "DoNot Call", "Wrong Num"
Set PasteSheet = Sheets("alumni_records")
Case Is = "No English", "Out Cntry", "Already Pl", _
"Yes Pledge", "Maybe Pledge", "No Pledge", "Unsp Pldg", "Spec Pldg"
Set PasteSheet = Sheets("supervisor_review")
End Select

On Error Resume Next
'copy data visible in column G, paste to corresponding sheet
DataRng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)
Next i
.AutoFilter 'turn off autofilter
End With
Application.ScreenUpdating = True

Sheets("alumni_records").Select
'add headings
Call Headings
Call columnfit
Range("A1").Select


Sheets("supervisor_review").Select
'add headings
Call Headings
Call columnfit
Range("A1").Select

Sheets(1).Select
Range("A1").Select

End Sub

Private Sub columnfit()

'format column width to autofit
Cells.Select
Selection.Columns.AutoFit

End Sub

Private Sub Headings()

'add column heading to all but sheet 1
Range("A1").Select
ActiveCell.FormulaR1C1 = "PROSPECT ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LAST NAME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "FIRST NAME"

Range("D1").Select
ActiveCell.FormulaR1C1 = "PHONE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "STANDARD COMMENTS"
Range("F1").Select
ActiveCell.FormulaR1C1 = "FREE COMMENTS"
Range("G1").Select
ActiveCell.FormulaR1C1 = "EXTENDED COMMENTS"
Range("H1").Select
ActiveCell.FormulaR1C1 = "OTHER"
Range("I1").Select
ActiveCell.FormulaR1C1 = "CODE"
Range("J1").Select
ActiveCell.FormulaR1C1 = "CALLER"
Range("A1").Select

End Sub

 

[Von Pookie]

Well, there *is* a limitation with autofilter that could be the issue--I haven't seen your data, though so not sure:

...the limitation of the amount of entries in the AutoFilter list is 1,000. If you have more than 1,000 unique items in the list, only the first 1,000 items appear.

If this is the issue, you could just use the advanced filter instead. Takes some different coding to run the filter, but the data copying would work just the same.

Another possibility is that the items in your list are different even though they don't look it. If the code is setting the filter for "Wrong Num", but there are some items listed on the sheet as "WrongNum" or some other variation, those will not be picked up by the filter, and not copied over.

Unfortunately at this exact moment, I don't have time to get you code for the advanced filter (I appear to have some actual work to do for once ) But it couldn't hurt you to read up on the advanced filter in the help files

 

[dalerguy]

I did verify the data are all the same. I'm processing 5714 records with 11 different autofilter items.

So I decided to import only 78 records for a test and still it will drop a few records. Weird. It all depends on how sheet 1 is sorted.

weird, I think i'm going to remove all formatting code I added and just use the import funtion and TEST() to see if it will capture all the records.

Thanks

 

[dalerguy]

I think i got it. My last column in sheet 1 wasn't a manditory field. So there may not be data in it. I took column I (filter column) which has a data in every field b/c it's manditory, and made it the last column in the range of data. Now all records are being captured.

I recall you giving me a tip with the copy feature of your code you provided. I was having weird results because my range has a extra column that was empty. SO there must be something weird happening when there is no data in the last column, which could happen b/c the field did always contain data.

I think that explains it... I hope anyway

seems I have it under control now

again thanks for you assistance.