有什么方法可以提高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屋！