VBA to filter multiple ranges in single column

Copper 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