Move rows without autofilter

[blinks]

The following code successfully moves rows meeting specific criteria, from one worksheet to another

Sheets("Data").Columns("I:I").AutoFilter Field:=1, Criteria1:="=*ebscohost*", Operator:= _
        xlAnd
Range("A1", Cells(Rows.count, "A").End(xlUp)).Resize(, 12).Copy Sheets("ebsco").Range("A1")
Sheets("Data").Range("A2", Range("A2").End(xlDown)).EntireRow.Delete
ActiveSheet.ShowAllData

However, the code fails when there are more than ~15K rows which meet the criteria. In such cases, nearly all rows in "Data" get moved to "ebsco", whether they meet the criteria or not.

I'd like to change the code so that it doesn't use AutoFilter at all. Can anyone help?

TIA

 

[Dryver14]

try

Sheets("Data").Range("A1", Cells(Rows.count, "A").End(xlUp)).Resize(, 12).Copy Sheets("ebsco").Range("A1")
Sheets("Data").Range("A2", Range("A2").End(xlDown)).EntireRow.Delete
ActiveSheet.ShowAllData

 

[blinks]

Thanks Dryver14, how do I accommodate Criteria1:="=*ebscohost*" within your suggested code?

TIA

 

[Dryver14]

when you copy this over to the next sheet do you over write everytime as I do not see a refernece to clearing sheet2,

this will be a time consuming operation as you will need to cut each line and paste to new sheet, are you saying the auto filter is failing or the copying across, if it does autofilter correctly the we may only need to adress the cut and paste issue.

I'm sure Autofilter is the way to go here

 

[blinks]

Thanks Dryver

The "ebsco" sheet is always cleared before the code above gets run. The auto filter is failing at the point of copying. When i step through the code, the correct autofilter displays on the "data" worksheet, but all of the worksheet except for the first 3-4 rows gets moved across to the ebsco sheet. I suspect I've encountered an excel bug here. I am able to successfully use the code above with other criteria requiring less than 15k rows auto filtered/copied. And the ebsco criteria yields 32k rows; but when I temporarily change some of the ebsco rows to something else to yield less than 15k rows, all works nicely.

 

[Dryver14]

I'm assuming this is a table or the flter would go out past column i
if so try the following.

Sheets("Data").Columns("I:I"). Criteria1:="=*ebscohost*", Operator:= _
        xlAnd
Range("A1").CurrentRegion.Copy Sheets("ebsco").Range("A1")
Sheets("Data").Range("A2", Range("A2").End(xlDown)).EntireRow.Delete
ActiveSheet.ShowAllData

 

[blinks]

Dryver, no go unfortunately. This code yields a syntax error.

I've noticed that if I try to do a manual autofilter on column I of the "reverse" of what I've got in the "Criteria1" field, viz. field does *not* contain ebscohost, I get the following error -

"Microsoft Excel cannot create or use the data range reference because it is too complex".

Column I contains URLs.

Have also tried using an advanced filter instead of AutoFilter, viz.

Sheets("Data").Columns("I:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Filter Criteria").Range("A6"), Unique:=False

, where A6 contains "ebscohost", but the filter does not get applied. Although this might be because it is expecting A6 to contain the full text of what it is supposed to filter, not just part.

 

[Dryver14]

I'm not really sure what you are filtering on now as in the first Macro Column I is the filter column and now you are talking about A6

 

[blinks]

Still filtering on column I, but grabbing the "ebscohost" filter criteria from cell a6 in sheet Filter Criteria. Just an experiment using Advanced Filter instead of AutoFilter. An unsuccessful experiment, unfortunately.

 

[Dryver14]

Ok no wories last question before the "Fix"

Does the delete part of the macro work right when you run it?