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.

 

[Marcelo Branco]

Be careful:Do you really want to delete the visible rows or to keep them?

M.

 

[kc_native]

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.

I got it to work using the following, I would just have to expand the array to contain all the numbers I want to see:

Dim StoredArrayString As Variant

StoredArrayString = Array("42488", "44044", "403028", "404089", "404212", "404471")
    With ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol))
        .AutoFilter Field:=2, Criteria1:=StoredArrayString, Operator:=xlFilterValues
    End With

Now my problem is I would like to delete all the hidden rows after filtering, and I am learning there is no opposite command to ".SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlUp)". I want to delete all the invisible rows, but that command doesn't exist.

 

[Marcelo Branco]

A possible workaround is to copy the visible rows to some place.and then delete the original data.. Not elegant, but should work.

M.

 

[kc_native]

A possible workaround is to copy the visible rows to some place.and then delete the original data.. Not elegant, but should work.

M.

HA! That's exactly what I just got done doing, in part by adding ".SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Clip Board").Range("A1")" to the autofilter With statement. Then I turn autofilter off and use "Rows("2:" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete (xlUp)" to delete all the data rows before copying what is on the clip board back to the sheet I did the autofilter in.

Thanks for putting up with this novice!

 

[mikerickson]

A criteria range has to have either a header or (if it's blank) formulas to determine which rows to show.
In your case, it needs a header for the 36 values that you are filtering on.

Try using Advanced Filter manually on your data to see if your Criteria Range is returning the rows you want.

 

[kc_native]

I did it manually when I recorded the macro, and all I did was highlight the range of numbers from row 1 to 36 in column 53. There was no header in that column and it filtered correctly when I completed the manual advanced filtering.

I just decided to stop messing around with advanced filter and am using different code to filter on an array of those numbers, then copying the visible rows to another sheet.

StoredArrayString = Array("42488", "44044", "403028", "404089", "404212", "404471")
    With ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 13))
        .AutoFilter Field:=2, Criteria1:=StoredArrayString, Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Clip Board").Range("A1")
    End With