如果使用 VBA 相鄰單元格不是空白，我需要自動編號行。
以下代碼中的任何一個都可以完美運行，除非它對抗空白單元格。
一如既往，非常感謝您的支持。
這是預期的輸出
Sub Fill_Serial_Numbers_Option1()
Dim LastRow As Long
LastRow = Cells(Rows.count, "B").End(xlUp).Row
If LastRow > 2 Then
Range("A3:A" & Application.Max(2, LastRow)) = Evaluate("ROW(A1:A" & LastRow & ")")
End If
End Sub
Sub Fill_Serial_Numbers_Option2()
Dim LastRow As Long
LastRow = Cells(Rows.count, "B").End(xlUp).Row
If LastRow > 2 Then
With Range("A3:A" & LastRow)
.Cells(1, 1).value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
End If
End Sub
uj5u.com熱心網友回復：
請測驗下一個代碼：
Sub testCountNonBlanks()
Dim sh As Worksheet, lastR As Long, arr, arrA, count As Long, i As Long
Set sh = ActiveSheet
lastR = sh.Range("B" & sh.rows.count).End(xlUp).row: count = 1
If lastR <= 2 Then Exit Sub
arr = sh.Range("B2:B" & lastR).value 'place the range in an array for faster iteration
arrA = sh.Range("A2:A" & lastR).value
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then arrA(i, 1) = count: count = count 1
Next i
sh.Range("A2").Resize(UBound(arrA), 1).value = arrA
End Sub
如果允許使用公式（用 VBA 撰寫），則可以使用下一個變體：
Sub testCountByFormula()
Dim sh As Worksheet, lastR As Long, rngB As Range
Set sh = ActiveSheet
lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
Set rngB = sh.Range("B2:B" & lastR)
sh.Range("A2:A10").Formula = "=IF(B2<>"""",COUNTA(" & rngB.Address & ")-COUNTA(" & rngB.Address(0, 1) & ") 1,"""")"
End Sub
uj5u.com熱心網友回復：
您不需要宏來完成此操作。假設您關心的所有內容是否為空白，那么您可以在 cell 中使用這樣的公式A9。=Counta($B$1:$B9)如果您有公式，您可以嘗試使用 COuntif 來利用某些東西。
uj5u.com熱心網友回復：
您可以使用從第一行到最后一行的回圈，如下所示：
Sub Fill()
Dim LastRow As Long
Dim Count As Integer
Dim Row As Integer
Count = 0
Row = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Do While Row <= LastRow
If Not (Cells(Row, 2) = "") Then
Count = Count 1
Cells(Row, 1) = Count
End If
Row = Row 1
Loop
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345267.html
上一篇：從模板添加作業簿并更改其名稱