Home

VBA to filter multiple ranges in single column

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

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies