哪位大神能否帮忙优化下代码（EXCEL_VBA），运行大量数据的时候就容易卡死

Question

哪位大神能否帮忙优化下代码（EXCEL_VBA），运行大量数据的时候就容易卡死.几万行还好，几十万就卡的要命

Sub vlookup()
Application.ScreenUpdating = False
Dim d As Object
Dim i As Long
Dim j As Long
Dim arr
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("Sheet1").Range("F2:G655350")
For i = 1 To UBound(arr)
d(arr(i, 1)) = arr(i, 2)
Next i
For j = 2 To 655350
With Sheets("Sheet1")
.Cells(j, 3).Value = d(.Cells(j, 1).Value)
End With
Next j
Application.ScreenUpdating = True
End Sub

Answer 1

修正  以此为准：

速度 最快的方法 只能 是用数组

Sub vlookup()
Application.ScreenUpdating = False
Dim d As Object
Dim i As Long
Dim j As Long
Dim arr
dim brr
dim crr (1 to 1000000,1 to 1)
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("Sheet1").range("F2:G"& sheets("sheet1").range("F1000000").end(3).row)
    For i = 1 To UBound(arr)
        d(arr(i, 1)) = arr(i, 2)
    Next i
 brr=Sheets("Sheet1").range("A2:A"& sheets("sheet1").range("A1000000").end(3).row)
    For j = 1 To UBound(brr)
      if d.exists(brr(j,1)) then crr(j,1)=d(brr(j,1))
    Next 
  Sheets("Sheet1").[c2].resize(j,1)=crr
Application.ScreenUpdating = True
End Sub

Answer 2

我的思路是求出表格F、G列最后一行有内容单元格位置，代替你上面的655350的数字：

a = Application.WorksheetFunction.Max(Cells(Rows.Count, 6).End(xlUp).Row, Cells(Rows.Count, 7).End(xlUp).Row)

如果F、G列最后一行有内容单元格位置相同的，则

a =Cells(Rows.Count, 6).End(xlUp).Row

这样可减小循环次数，提高运行速度。

Sub vlookup()
Application.ScreenUpdating = False
Dim d As Object
Dim a As Long
Dim i As Long
Dim j As Long
Dim arr
a= Application.WorksheetFunction.Max(Cells(Rows.Count, 6).End(xlUp).Row, Cells(Rows.Count, 7).End(xlUp).Row)
Set d = CreateObject("Scripting.Dictionary")
    arr = Sheets("Sheet1").Rangecells(cells(2,6),cells(a,7))
    For i = 1 To UBound(arr)
        d(arr(i, 1)) = arr(i, 2)
    Next i
    For j = 2 To a
        With Sheets("Sheet1")
            .Cells(j, 3).Value = d(.Cells(j, 1).Value)
        End With
    Next j
Application.ScreenUpdating = True
End Sub

本回答被网友采纳

Answer 3

655350 用 usedRange.rows.count 代替可以减少程序负担

第三列输入公式
=SUMPRODUCT(($G$2:$G$655350)*(ROW($F$2:$F$655350)=MAX(ROW($G$2:$G$655350)*($F$2:$F$655350=F2))))