How to assign filtered column data to a variable using Set in VBA excel?

Handsome Teh

5/3/24, 12:24 PM

I am newbie to VBA excel. I am trying to use Set to assign some data from a filtered column (through AutoFilter) to a variable (X in this case). Below shows the code and image of the table.

Sub Test()

    Dim X As Variant
    
    Worksheets("Sheet1").Range("B2").AutoFilter _
                                     Field:=2, _
                                     Criteria1:="1"

    With Worksheets("Sheet1")
        Set X = .Range("D3:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With
   
End Sub

The filtering works as I expected. However, I ended up setting the entire unfiltered data in the range of D3:D23 to X. How can I assign the filtered data from column D to X?

0 + 0

vbscript

filter

microsoft-excel

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How to assign filtered column data to a variable using Set in VBA excel?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.