余小章 @ 大內殿堂

以Office 2003為例，一個工作表有256*65536格，如果要填滿一張工作表，有下列兩種方法。

法一：土法鍊鋼，迴圈填入法，很簡單的用法，但效能很差。

Sub LoopFillRange()

    Dim myRows As Long, myCols As Integer

    Dim i As Long, r As Long

    Dim c As Integer

    Dim StartTime As Date, EndTime As Date

    Application.ScreenUpdating = False

    myRows = 65536

    myCols = 256

    ReDim myArray(1 To myRows, 1 To myCols)

    Cells.Clear

    StartTime = Timer

    i = 1

    For r = 1 To myRows

        For c = 1 To myCols

            Range("A1").Offset(r - 1, c - 1).Value = i '利用巢狀迴圈將值填入儲存格

            i = i + 1

        Next c

        DoEvents

    Next r

    EndTime = Timer

    MsgBox Format(EndTime - StartTime, "00.00") & "秒"

    Application.ScreenUpdating = True

End Sub

法二：陣列填入法，先將值填入陣列後，再將陣列填入工作表

    '利用陣列填滿一張工作表
    Dim myRows As Long, myCols As Integer
    Dim myArray() As Long
    Dim i As Long, r As Long
    Dim c As Integer
    Dim StartTime As Date, EndTime As Date
    myRows = 65536
    myCols = 256

    ReDim myArray(1 To myRows, 1 To myCols)
    Cells.Clear
    Application.ScreenUpdating = False
    StartTime = Timer
    i = 1
    For r = 1 To myRows
        For c = 1 To myCols
            myArray(r, c) = i '利用巢狀迴圈將值填入陣列
            i = i + 1
        Next c
    Next r
    
    '將陣列的值傳回儲存格
    Range(Cells(1, 1), Cells(myRows, myCols)) = myArray
    
    EndTime = Timer
    MsgBox Format(EndTime - StartTime, "00.00") & "秒"
    Application.ScreenUpdating = True
End Sub

範例下載：陣列-填滿工作表.rar

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET