CODEWITHSUNDEEP
arraysvbaautofilter
BRCoder
BRCoder

Reputation: 28

VBA: How do I filter by an array that I created from range of cells

I know I am creating the array correctly and I have used the autofilter code with arrays before but for some reason when I try to filter by a dynamic array only the first value of my array comes back. I create the array from an already filtered list on one sheet and then go to a different sheet to filter by the array values.

 Dim StepArray As Variant
 Dim LastRow As Long


  LastRow = Cells(Rows.Count, 1).End(-4162).Row
  StepArray = Range("C4:C" & LastRow).SpecialCells(xlCellTypeVisible).Value
  Sheet2.Select
  LastRow = Cells(Rows.Count, 1).End(-4162).Row
  Range(Cells(1, 1), Cells(LastRow, 5)).AutoFilter Field:=4, Criteria1:=StepArray, Operator:=xlFilterValues

  End Sub

Upvotes: 0

Views: 2364

Answers (2)

user3598756
user3598756

Reputation: 29421

edited to add the case the filtered range is a contiguous one

Range values to array doesn't work with non contiguous ranges like a filtered one is much likely to be

you have to loop through range and fill the array, like 

Function GetFilteredValues(rng As Range) As Variant
    Dim cell As Range
    Dim iCell As Long

    ReDim arr(1 To rng.Count)
    For Each cell In rng
        iCell = iCell + 1
        arr(iCell) = cell.Value
    Next
    GetFilteredValues = arr
End Function

to be used in your main code as follows

StepArray = GetFilteredValues(Range("C4:C" & LastRow).SpecialCells(xlCellTypeVisible))

Should filtered range be actually a contiguous one then you have to transpose it and get a one-dimensional array

StepArray = Application.Transpose(Range("C4:C" & LastRow).SpecialCells(xlCellTypeVisible).Value)

Upvotes: 1

JiheL
JiheL

Reputation: 106

StepArray is treated as a value because you didn't set it to be a range.

Set StepArray = Range("C4:C" & LastRow).SpecialCells(xlCellTypeVisible)

Maybe better? Just a suggestion, I never tried to filter with arrays.

Upvotes: 0

Related Questions