Excel macro freezing

Question

我有一个宏，它合并了一个旧工作表和一个新工作表(即更新旧工作表(。宏检查是否有值更改，如果有更改，则新宏中的新值将填充到旧宏中。

它运行了好几次，直到我尝试用包含1000个额外条目/行(约4000个条目(的新表更新旧表。当宏开始运行时，Excel底部的进度条开始显示闪烁的文本("计算8个线程"(。

这个问题的原因可能是什么？

Dim wb1 As Excel.Workbook
Set wb1 = Workbooks.Open(Orignial_excel)
Dim wb2 As Excel.Workbook
Set wb2 = Workbooks.Open(New_excel)

' get number of Rows in both the excels
Dim Ori_row_count
Dim New_row_count
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim BB
Dim FF
Dim Ori_row_count_Up
Dim New_row_count_UP
Set sh = wb1.Sheets("Detail")
Set sh1 = wb2.Sheets("FC Detail")
Ori_row_count = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
New_row_count = sh1.Range("A1", sh1.Range("A1").End(xlDown)).Rows.Count

' Find out actual row count .
' Original Excel
For BB = 2 To Ori_row_count + 1
If sh.Cells.Item(BB, 6) = Empty Then
Ori_row_count_Up = BB
Exit For
End If 
Next BB
Ori_row_count = Ori_row_count_Up
Ori_row_count = 743
For FF = 4 To New_row_count
If sh1.Cells.Item(FF, 6) = Empty Then
New_row_count_UP = FF
Exit For
End If
Next FF
New_row_count = New_row_count_UP

Answer 1

注意，这并不总是找到最后使用的行，但如果第1行和第2行中没有数据，它将找到1048576的最后一行

Ori_row_count = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
New_row_count = sh1.Range("A1", sh1.Range("A1").End(xlDown)).Rows.Count

因此，您的循环将迭代CCD_ 2次，因为有2个循环，它将迭代2次。

使用此项查找最后使用的行：

Dim LastUsedRow As Long
LastUsedRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row

看起来你使用循环只是为了找到F列中的第一个空单元格sh.Cells.Item(BB, 6) = Empty。我认为你可以通过替换循环来实现这一点

For BB = 2 To Ori_row_count + 1
If sh.Cells.Item(BB, 6) = Empty Then
Ori_row_count_Up = BB
Exit For
End If 
Next BB

只有

Dim NextEpmtyRowF As Long
NextEpmtyRowF = sh.Cells(2, "F").End(xlDown).Row + 1
If sh.Cells(2, "F").Value = vbNullString Then
NextEpmtyRowF = 2
ElseIf sh.Cells(3, "F").Value = vbNullString Or NextEpmtyRowF >= sh.Rows.Count Then
NextEpmtyRowF = 3
End If

如果您想要F列中所有数据之后的下一个epmty行，您甚至可以使用以下内容而不是循环：

Dim NextEpmtyRowF As Long
NextEpmtyRowF = sh.Cells(sh.Rows.Count, "F").End(xlUp).Row + 1