用indirect函数结合 3+8*row()、7+8*row()直接搞定。

O(∩_∩)O谢谢，以上是针对上述数据的宏代码，供大家学习参考


Private Sub CommandButton1_Click()
s = Sheets("Sheet2").[a10000].End(xlUp).Row
ar = Sheets("Sheet2").Range("a1:x10000")
ReDim arr(1 To s, 1 To 20)
k = 1

For i = 3 To s
If ar(i, 3) <> "" And ar(i, 4) = "" Then
arr(k, 1) = k
arr(k, 2) = ar(i, 3)
arr(k, 3) = ar(i + 4, 4)
arr(k, 4) = ar(i + 4, 6)
arr(k, 5) = ar(i + 4, 7)
arr(k, 6) = ar(i + 4, 8)
arr(k, 7) = ar(i + 4, 11)
arr(k, 8) = ar(i + 4, 15)
arr(k, 9) = ar(i + 4, 17)
arr(k, 10) = ar(i + 4, 18)
arr(k, 11) = ar(i + 4, 19)
arr(k, 12) = ar(i + 4, 20)
k = k + 1
End If

Next i
[a2].Resize(s, 20) = arr
End Sub