VBA Excel 365 issue on AutoFilter Field:=X, Criteria1

Question

Dear all,

by this code I was able to filter in a PowerPivot all the values greater than zero using Excel 2013 VBA:

ReDim MyArray(1 To LastRowIndexForPivot - 3) 

For i = 4 To LastRowIndexForPivot 

    If ActiveWorkbook.Sheets("PivotStock").Cells(i, 4).Value > 0 Then 

        Cont = Cont + 1 

            MyValue = ActiveWorkbook.Sheets("PivotStock").Cells(i, 4).Value 

            MyArray(Cont) = CStr(MyValue) 

    End If 

Next i 

ActiveWorkbook.Sheets("PivotStock").Range("$A:$D").AutoFilter Field:=4, Criteria1:=MyArray, Operator:=xlFilterValues 

ActiveWorkbook.Sheets("PivotStock").Range("A4").Select

Now I have installed Office 365 and the above does not apply any filter: any suggestion to fix it?

I have also tried to register the macro and this the resulting code:

    Range("A3").Select 

    ActiveSheet.Range("$A$3:$D$32").AutoFilter Field:=4, Criteria1:=Array("1", "9"), Operator:=xlFilterValues

but when I run the above code I get this error message:

Thanks

Answer 1

Hello lucausa75

Welcome to Microsoft Community.

According to your current description, it seems that VBA code in older versions of Excel 2013 will have a run-time error '1004' when running in new versions of Excel 365.

The issue might be caused by the way the Criteria1 parameter is passed to the AutoFilter method. Instead of passing an array of strings as Criteria1, you can try passing a comma-separated string of values enclosed in double quotes.

For example, replace this line:<br><br><br><br><br> ActiveWorkbook.Sheets("PivotStock").Range("$A:$D").AutoFilter Field:=4, Criteria1:=MyArray, Operator:=xlFilterValues <br><br><br><br><br>with this line:<br><br><br><br><br> ActiveWorkbook.Sheets("PivotStock").Range("$A:$D").AutoFilter Field:=4, Criteria1:=Join(MyArray, ","), Operator:=xlFilterValues

This should concatenate the values in the MyArray variable into a string separated by commas, which can then be used as the Criteria1 parameter in the AutoFilter method.

Alternatively, you can also try passing the Criteria1 parameter as a variant array instead of a string array. To do this, declare the MyArray variable as a variant instead of a string array, and use the Transpose function to convert the vertical array into a horizontal array.

For example, replace this line:<br><br><br><br><br> ReDim MyArray(1 To LastRowIndexForPivot - 3) <br><br><br><br><br>with this line:<br><br><br><br><br> Dim MyArray As Variant <br><br><br><br> MyArray = Application.Transpose(ActiveSheet.Range("D4:D" & LastRowIndexForPivot).Value)

Then, replace this line:<br><br><br><br><br> ActiveWorkbook.Sheets("PivotStock").Range("$A:$D").AutoFilter Field:=4, Criteria1:=MyArray, Operator:=xlFilterValues <br><br><br><br><br>with this line:<br><br><br><br><br> ActiveWorkbook.Sheets("PivotStock").Range("$A:$D").AutoFilter Field:=4, Criteria1:=Array(MyArray), Operator:=xlFilterValues

This should pass the Criteria1 parameter as a variant array, which might work better with the AutoFilter method. Here's an updated version of the VBA code that should work in Excel 365:

Sub FilterGreaterThanZero()

Dim ws As Worksheet 

Dim LastRowIndexForPivot As Long 

Dim MyArray As Variant 

Dim Cont As Long 

Dim i As Long 

Set ws = ActiveWorkbook.Sheets("PivotStock") 

LastRowIndexForPivot = ws.Range("D" & ws.Rows.Count).End(xlUp).Row 

' Store values greater than zero in an array 

ReDim MyArray(1 To LastRowIndexForPivot - 3) 

Cont = 0 

For i = 4 To LastRowIndexForPivot 

    If ws.Cells(i, 4).Value > 0 Then 

        Cont = Cont + 1 

        MyArray(Cont) = CStr(ws.Cells(i, 4).Value) 

    End If 

Next i 

' Apply filter 

ws.Range("$A:$D").AutoFilter Field:=4, Criteria1:=Join(MyArray, ","), Operator:=xlFilterValues 

' Clear selection 

ws.Range("A1").Select 

End Sub

This code stores the active worksheet in a variable (ws), uses the End(xlUp) method to find the last row index for the pivot table, and clears the selection after applying the filter.

Please note that the filter might not work as expected if there are blank cells or errors in the column being filtered. Feel free to let me know how it goes.

Best regards,

Chandy |Microsoft Community Support Specialist

Answer 2

Thanks a lot for your help.

I have tried your code but I get the below error on this line:

ws.Range("$A:$D").AutoFilter Field:=4, Criteria1:=Join(MyArray, ","), Operator:=xlFilterValues

but I have solved by changing the above line to:

WS.Range("D1").AutoFilter Field:=4, Criteria1:=MyArray, Operator:=xlFilterValues

Answer 3

Hello lucausa75

Sorry for my late response.

If you're getting an error on the line, it's possible that the MyArray variable is empty or contains only empty elements. And I am glad to hear that you were able to solve the issue!

The reason why changing the filter line from to worked is that the latter is specifying a specific starting cell for the filter range (D1) instead of filtering the entire range A:D. On the other hand, when you use , it filters only the data range in column D starting from cell D1, which should not include any empty rows or cells if the pivot table is structured correctly. This can make the filtering process more efficient and less prone to errors.

So, in summary, it's a good practice to specify the starting cell for the filter range to avoid issues with empty cells or rows.

Answer 4

Filtering the entire range A:D is working with VBA Excel 2013 but it seems not more working with the new version 365

Answer 5

We need your file for testing, everything else is like reading from a crystal ball.

Andreas.