Advanced Filtering VBA Code Issue

[kc_native]

I am trying to do an advanced filter in VBA and the code structure came from a macro I recorded, with some changes to range references. I am getting the following error: "Run-time error '1004': Application-defined or object-defined error. I would appreciate if someone could help me identify where in the code I have gone wrong. In the previous section of the code I am creating a sheet two and renaming it Target Projects. Do I need to add that new sheet name to the "CopyToRange=Range(A1)" portion of the code? The Target Project sheet is activated after adding it and re-naming it, before this section of the code executes.

[B][COLOR=#0000FF]Sheets("DATA80100").Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet).xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Range("A1"), Unique:=False[/COLOR][/B]

Thanks for any help you can give this novice.

 

[kc_native]

I'm not sure if the criteria range in another workbook is the culprit (not tested) but, for sure, you must specify the location of the criteria range in such workbook (sheet and range)

M.

This is what the code should have looked like when I copied it into my post, and as you can see, I am referencing the sheet and range for the criteria.

[[B][COLOR=#0000ff]Sheets("DATA80100").Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet.xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Range("A1"), Unique:=False[/COLOR][/B]/CODE]

 

[Norie]

If you mean the workbook 'Macro Launch Sheet.xlsm' isn't open then I'm pretty sure that could be a problem.

 

[Marcelo Branco]

This is what the code should have looked like when I copied it into my post, and as you can see, I am referencing the sheet and range for the criteria.

[[B][COLOR=#0000ff]Sheets("DATA80100").Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet.xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Range("A1"), Unique:=False[/COLOR][/B]/CODE][/QUOTE]

Questions
Why are you using BA1:BA36?  
BA1:BA36 contains texts or numbers?
Is this an array of criteria? If so, i *think* it should be a horizontal array - maybe with Application.Transpose(....) 

M.

 

[Marcelo Branco]

oops...

Disregard this
"If so, i *think* it should be a horizontal array - maybe with Application.Transpose(....) "

I was thinking when we use an array of criteria in normal filter. In advanced filter an OR criteria should be vertical.

M.

 

[kc_native]

If you mean the workbook 'Macro Launch Sheet.xlsm' isn't open then I'm pretty sure that could be a problem.

No, it's open. It's the workbook the macro is executed from, so it will always be open when this code is run.

 

[kc_native]

oops...

Disregard this
"If so, i *think* it should be a horizontal array - maybe with Application.Transpose(....) "

I was thinking when we use an array of criteria in normal filter. In advanced filter an OR criteria should be vertical.

M.

I have re-written the code to copy the range of criteria into the worksheet I am filtering from so I am no longer referencing an external worksheet, but I still get the same error.

Maybe I should stop trying to use the advanced filter, and just make a copy of the worksheet, and then do a For Loop to delete all rows that don't contain the project numbers in the criteria? So use an index or array of numbers to compare to that would determine whether the row should stay or be deleted? I know someone on this board gave me some code a long time ago that would do something like that, but don't think I kept it.

What say you? Do you have any ideas on how it could be done other than advanced filtering?

Thanks!

 

[Marcelo Branco]

You didn't answer the question about what AB1:AB36 (criteria range) contains.
Could you please tell us?

M.

 

[kc_native]

You didn't answer the question about what AB1:AB36 (criteria range) contains.
Could you please tell us?

M.

Sorry, it is just a list of 30 or so 5 and 6-digit numbers that represent projects we analyze. So it looks like the following when placed in a column:

567234
789101
231223
121245
99872
and so on....

 

[kc_native]

You didn't answer the question about what AB1:AB36 (criteria range) contains.
Could you please tell us?

M.

Found this code in my notes. I could just make a copy of the worksheet with the data in it, and the put my list of project numbers in the filter criteria array to filter the data in the new tab I just created. What do you think?

With Range("B:B”)
.AutoFilter Field:=2, Criteria1 :=Array(“xxxxxx”,”xxxxx”,"xxxxxx”), Operator = xlFilterValues
.SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlUp) 
End With

If I use numbers in the array, do I need to include the quotes around each number?

 

[Marcelo Branco]

I think this is fine, but be aware that if the criteria array is vertical and contains numbers (not text) you must use something like

Dim v as Variant

v = Split(Join(Application.Transpose(Range("AB1:AB36"))))
With Range("B:B”)
  .AutoFilter Field:=2, Criteria1 :=v, Operator = xlFilterValues
  .SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlUp) 
End With

Take a look at this thread
https://www.mrexcel.com/forum/excel...er-multiple-criteria-not-working-numbers.html

M.