有什么方法可以提高50000行的循环速度? [英] Any method to improve the looping speed of 50000 more rows?
问题描述
Sub CopyRowsifDuplicate()
调暗范围,b作为范围,Newloans作为整数
Dim c作为范围,d作为范围,Matloans作为整数
Dim e As Range,f As Range,Existloans As Integer
申请
      .Calculation = xlCalculationManual
      .ScreenUpdating =假
结束用于
'移动新贷款
每张a In Sheets("输出")。范围("A1:A50000")
每个b表格("今天")。范围("A1:A50000")
Newloans = Sheets("New loans")。Range("A"& Rows.Count).End(xlUp).Row + 1
如果a = b Then Sheets("New"贷款")。范围("A"和"Newloans".EntireRow.Value =表格("今天")。范围("A"& b.Row).EntireRow.Value
下一步b
下一个a
'移动成熟贷款
每张c在表格中("输出" ;)。范围("B1:B50000")
每张表格("上一页")。范围("A1:A50000")
Matloans =表格("成熟贷款")。范围("A"& Rows.Count).End(xlUp).Row + 1
如果c = d然后表格("成熟贷款" ;)。范围("A"和"Matloans".EntireRow.Value =表格(&qu) OT;前")范围(" A" &安培; d.Row).EntireRow.Value
下一个d
下一个c
$
'移动现有贷款
对于每个e表格("输出")。范围("C1:C50000")
对于每个f表格("上一个")。范围(" ; A1:A50000")
Existloans = Sheets("现有贷款")。范围("A"& Rows.Count).End(xlUp).Row + 1
如果e = f则表格("现有贷款")。范围("A"和"Existloans".EntireRow.Value =表格("上一页")。范围("A"& f .Row).EntireRow.Value
下一页f
$
下一页e
$
结束子¥ b $ b
Sub CopyRowsifDuplicate()
Dim a As Range, b As Range, Newloans As Integer
Dim c As Range, d As Range, Matloans As Integer
Dim e As Range, f As Range, Existloans As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Move New loans
For Each a In Sheets("Output").Range("A1:A50000")
For Each b In Sheets("Today").Range("A1:A50000")
Newloans = Sheets("New loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If a = b Then Sheets("New loans").Range("A" & Newloans).EntireRow.Value = Sheets("Today").Range("A" & b.Row).EntireRow.Value
Next b
Next a
'Move Mature loans
For Each c In Sheets("Output").Range("B1:B50000")
For Each d In Sheets("Previous").Range("A1:A50000")
Matloans = Sheets("Mature loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If c = d Then Sheets("Mature loans").Range("A" & Matloans).EntireRow.Value = Sheets("Previous").Range("A" & d.Row).EntireRow.Value
Next d
Next c
'Move Existing loans
For Each e In Sheets("Output").Range("C1:C50000")
For Each f In Sheets("Previous").Range("A1:A50000")
Existloans = Sheets("Existing loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If e = f Then Sheets("Existing loans").Range("A" & Existloans).EntireRow.Value = Sheets("Previous").Range("A" & f.Row).EntireRow.Value
Next f
Next e
End Sub
推荐答案
该代码很复杂,似乎要进行3万次50,000 * 50,000次迭代。你无法加快代码速度。那么,你想做什么?请提供5行数据示例,因为我确信不同的解决方案会明显更快。我怀疑
是vlookup和VBA的组合。
That code's complex and appears to be doing 50,000 * 50,000 iterations 3 times. You can't speed that code up. So, what are you trying to do? Please provide a 5 lines of data example as I'm sure a different solution will be significantly quicker. I suspect a combination of vlookup and VBA will be the thing.
这篇关于有什么方法可以提高50000行的循环速度?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!