如何在VBA中编写有变量的数组公式。

Question

在BE2单元格输入数组公式：=INDEX(B:B,SMALL(IF(MATCH($B$1:$B$337,$B$1:$B$337,0)=ROW($1:$337),ROW($1:$337),65536),ROW(2:2))),并填充至BE337。
如果用VBA输入数组公式并赋值，且行数是变量，并删除该列为0的内容。请问：如何编写代码？
请教：在BE2：BE&i单元格区域里输入数组公式并赋值：=INDEX(B:B,SMALL(IF(MATCH($B$1:$B$&i,$B$1:$B$&i,0)=ROW($1:$&i),ROW($1:$&i),65536),ROW(2:2)))；赋值后，删除该列为0的内容。目的是将B列中的姓名提取出来，重复的只取一个。请问：如何编写代码？

Answer 1

前一句写入数组公式，后一句公式向下填充，最后用循环清除0值的单元格数据

代码如下

Sub tt()
    Range("BE2").FormulaArray = "=INDEX(B:B,SMALL(IF(MATCH($B$1:$B$337,$B$1:$B$337,0)=ROW($1:$337),ROW($1:$337),65536),ROW(2:2)))"
    AutoFill Destination:=Range("BE2:BE337")
    For x = 2 To 337
        If Range("BE" & x) = 0 Then Range("BE" & x).ClearContents
    Next x
End Sub

追问

谢谢你的回答！在已确定是337行时，可以用你的代码。如果行数是不确定的变量（就是不知道最后一行数是多少），如何编写代码呢？

追答

你最后一行以什么列来定位？你提问就是337行呀
最后一行可以用sheet1.usedrange.rows
来定位

追问

可以用B列。就是不知道怎么写进代码里。请指教！

追答

我是说，你原来是B列后面是空的，对吧？那就不能以B列来定位，所以用usedrange.rows

追问

B列是姓名，BE列才是空的。

追答

那就是说以B列最后一行为准是吧？加个变量，修改如下

Sub tt()
    dim i&
    i=range("B65536").end(xlup).row
    Range("BE2").FormulaArray = "=INDEX(B:B,SMALL(IF(MATCH($B$1:$B$" & i & ",$B$1:$B$  & i & ",0)=ROW($1:$" & i & "),ROW($1:$" & i & "),65536),ROW(2:2)))"
    AutoFill Destination:=Range("BE2:BE" & i)
    For x = 2 To i
        If Range("BE" & x) = 0 Then Range("BE" & x).ClearContents
    Next x
End Sub

Answer 2

好牛逼的样子