Home

VBA to filter multiple ranges in single column

%3CLINGO-SUB%20id%3D%22lingo-sub-377579%22%20slang%3D%22en-US%22%3EVBA%20to%20filter%20multiple%20ranges%20in%20single%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377579%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20a%20macro%20created%20which%20filters%20different%20groups%20of%20numbers%20from%20a%20within%20a%20possible%20range%20(35000-39999)%20in%20a%20single%20column%20and%20copied%20the%20filtered%20lines%20to%20a%20new%20tab.%20this%20works%20on%20a%20loop%20to%20create%20multiple%20tabs%20for%20each%20division%20as%20necessary.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20worked%20perfectly%20but%20a%20new%20set%20of%20numbers%20have%20been%20added%20within%20the%20total%20range%20but%20each%20new%20number%20relates%20to%20a%20different%20division%20and%20i%20cannot%20get%20it%20added%20correctly%20into%20my%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20working%20code%20was%26nbsp%3B%3C%2FP%3E%3CP%3EActiveSheet.Range(%22%24A%241%3A%24Q%24%22%20%26amp%3B%20lRow).AutoFilter%20Field%3A%3D4%2C%20Criteria1%3A%3D%22%26gt%3B%3D35000%22%2C%20_%3CBR%20%2F%3EOperator%3A%3DxlAnd%2C%20Criteria2%3A%3D%22%26lt%3B%3D35119%22%3CBR%20%2F%3EIf%20Range(%22A1%3AA%22%20%26amp%3B%20lRow).SpecialCells(xlCellTypeVisible).Count%20%26gt%3B%201%20Then%3CBR%20%2F%3EActiveSheet.Range(%22%24A%241%3A%24Q%24%22%20%26amp%3B%20lRow).SpecialCells%20_%3C%2FP%3E%3CP%3E(xlCellTypeVisible).EntireRow.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20tried%20to%20change%20it%20to%26nbsp%3B%3C%2FP%3E%3CP%3EActiveSheet.Range(%22%24A%241%3A%24Q%24%22%20%26amp%3B%20lRow).AutoFilter%20Field%3A%3D4%2C%20Criteria1%3A%3DArray(%22%26gt%3B%3D35000%22%2C%20%22%26lt%3B%3D35119%22%2C%20%22%3D38470%22)%3CBR%20%2F%3EIf%20Range(%22A1%3AA%22%20%26amp%3B%20lRow).SpecialCells(xlCellTypeVisible).Count%20%26gt%3B%201%20Then%3CBR%20%2F%3EActiveSheet.Range(%22%24A%241%3A%24Q%24%22%20%26amp%3B%20lRow).SpecialCells%20_%3CBR%20%2F%3E(xlCellTypeVisible).EntireRow.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20does%20not%20pull%20out%20all%20the%20data%20correctly%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20i%20add%20'Operator%3DxlFilterValues'%26nbsp%3Bit%20creates%20error%201004%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20on%20how%20i%20correct%20this%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-377579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067011%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20filter%20multiple%20ranges%20in%20single%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067011%22%20slang%3D%22en-US%22%3EActiveSheet.Range(%22%24A%241%3A%24AT%24670%22).AutoFilter%20Field%3A%3D8%2C%20Operator%3A%3DxlFilterValues%2C%20Criteria1%3A%3DArray(%22%3D7*%22%2C%20%22%3D6*%22)%3CBR%20%2F%3E%3CBR%20%2F%3Eneed%20to%20add%20%22%3D9*%22%20help%20me%20to%20find%20out%20the%20solution%3C%2FLINGO-BODY%3E
Mark Lee
Occasional Contributor

Hi,

 

I had a macro created which filters different groups of numbers from a within a possible range (35000-39999) in a single column and copied the filtered lines to a new tab. this works on a loop to create multiple tabs for each division as necessary. 

 

This worked perfectly but a new set of numbers have been added within the total range but each new number relates to a different division and i cannot get it added correctly into my macro.

 

The working code was 

ActiveSheet.Range("$A$1:$Q$" & lRow).AutoFilter Field:=4, Criteria1:=">=35000", _
Operator:=xlAnd, Criteria2:="<=35119"
If Range("A1:A" & lRow).SpecialCells(xlCellTypeVisible).Count > 1 Then
ActiveSheet.Range("$A$1:$Q$" & lRow).SpecialCells _

(xlCellTypeVisible).EntireRow.Copy

 

i tried to change it to 

ActiveSheet.Range("$A$1:$Q$" & lRow).AutoFilter Field:=4, Criteria1:=Array(">=35000", "<=35119", "=38470")
If Range("A1:A" & lRow).SpecialCells(xlCellTypeVisible).Count > 1 Then
ActiveSheet.Range("$A$1:$Q$" & lRow).SpecialCells _
(xlCellTypeVisible).EntireRow.Copy

 

This does not pull out all the data correctly 

if i add 'Operator=xlFilterValues' it creates error 1004

 

Any advice on how i correct this

Mark

 

 

1 Reply
ActiveSheet.Range("$A$1:$AT$670").AutoFilter Field:=8, Operator:=xlFilterValues, Criteria1:=Array("=7*", "=6*")

need to add "=9*" help me to find out the solution
Related Conversations
Page properties not visible for filtering news webpart
stevenam in SharePoint on
12 Replies
How do I Find/Replace or Filter with exact words/characters?
MattC475 in Excel on
5 Replies
vba sending email w/ attachment
katrina bethea in Excel on
7 Replies
VBA - Splitting worksheet by Dept
Zia Siddique in Excel on
12 Replies
Userform Listbox HELP
R3d3mpt10n in Word on
1 Replies