Search code examples
excelvba

Filter Range C3:G3 And paste to other cells

I have values in a range C3:G3. I have set up a filter that works if you look at the watch area. When it pastes the output it only pastes the first item in the filtered range. Here is the code. I thought maybe I had to transpose it. So the transpose was added. Both give the same single output.

Sub filter_test()
Dim PhoneAry As Variant
Dim myAry(0 To 4) As String


myAry(0) = Range("C3").Value
myAry(1) = Range("D3").Value
myAry(2) = Range("E3").Value
myAry(3) = Range("F3").Value
myAry(4) = Range("G3").Value

PhoneAry = filter(myAry, "Wireless")
    
Dim Destination As Range
Set Destination = Range("C4:G4")
Set Destination = Destination.Resize(UBound(PhoneAry), 1)

Destination.Value = Application.transpose(PhoneAry)
End sub

Here is what it looks like. Finished Output

Solution

  • PhoneAry is indexed from 0. In Resize you should add 1.

    Sub filter_test()
Dim PhoneAry As Variant
Dim myAry(0 To 4) As String


myAry(0) = Range("C3").Value
myAry(1) = Range("D3").Value
myAry(2) = Range("E3").Value
myAry(3) = Range("F3").Value
myAry(4) = Range("G3").Value

PhoneAry = Filter(myAry, "Wireless")
    
Dim Destination As Range
Set Destination = Range("C4:G4")
Set Destination = Destination.Resize(1, UBound(PhoneAry) + 1)

Destination.Value = PhoneAry
End Sub