編輯：通過列印陣列，我的意思是將陣列放在作業表中的一個范圍內:)
我在帶有 VBA 的 excel 表上使用以下代碼。這將行與匹配的銷售代表相結合。下面是源表。這被加載到一個陣列中。
運行以下代碼后，行被合并，我將合并的行歸零。我的挑戰是列印某些列并且只列印非空行。為了實作這一點，我試圖遍歷陣列并創建另一個僅包含非空行的陣列。
Sub mergeCategoryValues2()
Dim arr2 As Variant
Dim rowcount As Long
Dim i As Variant
Dim colcount As Long
arr2 = ActiveSheet.ListObjects("APPLE").Range
rowcount = UBound(arr2, 1)
colcount = UBound(arr2, 2)
For i = rowcount To 2 Step -1
If arr2(i, 3) = arr2(i - 1, 3) Then
arr2(i - 1, 6) = arr2(i - 1, 6) arr2(i, 6)
For k = 1 To colcount
arr2(i, k) = Null 'this loop is probably not required i can probably just use the first column
Next k
End If
Next i
End Sub
最終我只想列印非空行和第 3、2 和 6 列。我認為最好的方法是創建一個包含非空行的陣列
uj5u.com熱心網友回復：
為結果創建一個與資料陣列具有相同行數的陣列。向下掃描資料行，并在 C 列中每次更改值時為結果陣列增加一個行計數器。使用調整大小轉儲結果的使用部分。
Option Explicit
Sub mergeCategoryValues2()
Dim arr2 As Variant, arOut As Variant
Dim rowcount As Long, colcount As Long
Dim i As Long, k As Long
arr2 = ActiveSheet.ListObjects("APPLE").Range
rowcount = UBound(arr2, 1)
colcount = UBound(arr2, 2)
ReDim arOut(1 To rowcount, 1 To 3)
For i = 2 To rowcount
If arr2(i, 3) = arr2(i - 1, 3) Then
arOut(k, 3) = arOut(k, 3) arr2(i, 6)
Else
k = k 1
arOut(k, 1) = arr2(i, 2)
arOut(k, 2) = arr2(i, 3)
arOut(k, 3) = arr2(i, 6)
End If
Next i
Sheet2.Range("A2").Resize(k, 3).Value2 = arOut
MsgBox "OK"
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/371426.html