Excel VBA Code to Filter on Multiple Values Excluding Blanks

Question

I am trying to create a filter that will filter values gathered by a dynamic array. The below code works for this, however it is also including blank values when filtering. If I add a second cirteria, this does not seem to work either. any help would be appreciated.

Sub AppToServerFilter()

Dim Apps() As String, size As Integer, i As Integer

'creates an array and fills it with values in the checksheet
With Sheets("CheckSheet")
Sheets("CheckSheet").Activate
size = WorksheetFunction.CountA(Worksheets("CheckSheet").Columns(1))
ReDim Apps(size)
For i = 1 To size
    Apps(i) = Cells(i, 1).Value
Next i
End With

'Commented out the array print
'For i = LBound(Apps) To UBound(Apps)
'txt = txt & Apps(i) & vbCrLf
'Next i
'MsgBox txt

'filters for all values in the array created above
Worksheets("App-to-Server").Select

'Range("A6").AutoFilter
'Range("A6").AutoFilter Field:=8, Criteria1:=Apps(), Operator:=xlFilterValues
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:=Apps(), Operator:=xlFilterValues

End Sub

Where did you add your second criteria? Did you read on AutoFilter? See here on how parameters are to be used: msdn.microsoft.com/en-us/library/office/ff193884.aspx — Luuklag
– Luuklag, Commented Jul 28, 2015 at 7:28
Have you checked that there are no blank rows in column 1 of CheckSheet? — Excel Developers
– Excel Developers, Commented Jul 28, 2015 at 10:32

Peaches · Accepted Answer · 2015-07-29 00:07:05Z

1

Apps() is a zero based array. Make the following changes:

ReDim Apps(size - 1)

For i = 1 To size
    Apps(i - 1) = Cells(i, 1).Value
Next i

answered Jul 29, 2015 at 0:07

Peaches

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Code to Filter on Multiple Values Excluding Blanks

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related