加快你的代码运行速度

shaokui123

优秀作品,学习一下

gulichali

斑竹，我有个代码，运行起来excel很卡很慢 不停的闪一会，体验很差 能否改的流畅一些呢？，以下是代码：
Private Sub Worksheet_Change(ByVal TargetAs Range)
Rows("23").EntireRow.Hidden =True
Rows("107:111").EntireRow.Hidden= True
Rows("149:153").EntireRow.Hidden= True
If Range(“C14”) = "无斜撑"Then
Rows("34").EntireRow.Hidden =True
Else
Rows("34").EntireRow.Hidden =False
End If
If Range(“C70”) = "×" Then
Rows("74:106").EntireRow.Hidden =True
Rows("112:115").EntireRow.Hidden= True
ElseIf Range(“C23”) = "0" AndRange(“C70”) = "√" Then
Rows("83:87").EntireRow.Hidden =True
Rows("74:82").EntireRow.Hidden =False
Rows("88:93").EntireRow.Hidden =False
Rows("98").EntireRow.Hidden =False
Rows("112:113").EntireRow.Hidden= False
Rows("101:105").EntireRow.Hidden= False
ElseIf Range(“C23”) = "1" AndRange(“C70”) = "√" Then
Rows("83:87").EntireRow.Hidden =False
Rows("74:82").EntireRow.Hidden =False
Rows("88:93").EntireRow.Hidden =False
Rows("98").EntireRow.Hidden =False
Rows("112:113").EntireRow.Hidden= False
Rows("101:105").EntireRow.Hidden= False
End If
If Range(“C93”) <> "不增加" AndRange(“C70”) = "√" Then
Rows("94:97").EntireRow.Hidden =False
Else
Rows("94:97").EntireRow.Hidden =True
End If
If Range(“C98”) = "增加" AndRange(“C70”) = "√" Then
Rows("99:100").EntireRow.Hidden =False
Rows("115").EntireRow.Hidden =False
Rows("105").EntireRow.Hidden =True
Rows("113").EntireRow.Hidden =True
ElseIf Range(“C98”) = "不增加" AndRange(“C70”) = "√" Then
Rows("99:100").EntireRow.Hidden =True
Rows("115").EntireRow.Hidden =True
Rows("105").EntireRow.Hidden =False
Rows("113").EntireRow.Hidden =False
End If
If Range(“C11”) = "1.3" AndRange(“C70”) = "√" And Range(“C93”) = "采用现浇" Then
Rows("114").EntireRow.Hidden =False
Rows("106").EntireRow.Hidden =False
Else
Rows("114").EntireRow.Hidden =True
Rows("106").EntireRow.Hidden =True
End If
If Range(“C71”) = "×" Then
Rows("116:148").EntireRow.Hidden= True
Rows("154:157").EntireRow.Hidden= True
ElseIf Range(“C23”) = "0" AndRange(“C71”) = "√" Then
Rows("125:129").EntireRow.Hidden= True
Rows("116:124").EntireRow.Hidden= False
Rows("130:135").EntireRow.Hidden= False
Rows("140").EntireRow.Hidden =False
Rows("154:155").EntireRow.Hidden= False
Rows("143:147").EntireRow.Hidden= False
ElseIf Range(“C23”) = "1" AndRange(“C71”) = "√" Then
Rows("125:129").EntireRow.Hidden= False
Rows("116:124").EntireRow.Hidden= False
Rows("130:135").EntireRow.Hidden= False
Rows("140").EntireRow.Hidden =False
Rows("154:155").EntireRow.Hidden= False
Rows("143:147").EntireRow.Hidden= False
End If
If Range(“C135”) <> "不增加" AndRange(“C71”) = "√" Then
Rows("136:139").EntireRow.Hidden= False
Else
Rows("136:139").EntireRow.Hidden= True
End If
If Range(“C140”) = "增加" AndRange(“C71”) = "√" Then
Rows("141:142").EntireRow.Hidden= False
Rows("157").EntireRow.Hidden =False
Rows("147").EntireRow.Hidden =True
Rows("155").EntireRow.Hidden =True
ElseIf Range(“C140”) = "不增加" AndRange(“C71”) = "√" Then
Rows("141:142").EntireRow.Hidden= True
Rows("157").EntireRow.Hidden =True
Rows("147").EntireRow.Hidden =False
Rows("155").EntireRow.Hidden =False
End If
If Range(“C11”) = "1.3" AndRange(“C71”) = "√" And Range(“C135”) = "采用现浇" Then
Rows("156").EntireRow.Hidden =False
Rows("148").EntireRow.Hidden =False
Else
Rows("156").EntireRow.Hidden =True
Rows("148").EntireRow.Hidden =True
End If
If Range(“C72”) = "×" Then
Rows("158:195").EntireRow.Hidden= True
ElseIf Range(“C72”) = "√" Then
Rows("158:168").EntireRow.Hidden= False
Rows("174").EntireRow.Hidden =False
Rows("178:183").EntireRow.Hidden= False
Rows("187:192").EntireRow.Hidden= False
End If
If Range(“C72”) = "√" AndRange(“C168”) <> "不增加" Then
Rows("169:173").EntireRow.Hidden= False
Else
Rows("169:173").EntireRow.Hidden= True
End If
If Range(“C72”) = "√" AndRange(“C174”) <> "不增加" Then
Rows("175:177").EntireRow.Hidden= False
Else
Rows("175:177").EntireRow.Hidden= True
End If
If Range(“C72”) = "√" AndRange(“C168”) = "采用现浇" And Range(“C11”) = "1.3" Then
Rows("184:186").EntireRow.Hidden= False
Rows("193:195").EntireRow.Hidden= False
Else
Rows("184:186").EntireRow.Hidden= True
Rows("193:195").EntireRow.Hidden= True
End If
If Range(“C73”) = "×" Then
Rows("196:220").EntireRow.Hidden= True
ElseIf Range(“C73”) = "√" Then
Rows("196:220").EntireRow.Hidden= False
End If
End Sub

zhanger

gulichali 发表于 2017-12-29 10:36
斑竹，我有个代码，运行起来excel很卡很慢 不停的闪一会，体验很差 能否改的流畅一些呢？，以下是代码：
P ...

我来说说，看是否在点了上。
第一，IF  ELSE  ENDIF 的 ELSE中有AND句法，这是一个原因。重要的是这个AND可以不要
        If Range("C70") = "×" Then          ElseIf Range("C23") = "0" And Range("C70") = "√" Then
难道C70中还有第三种情况？
第二，可以用UNION将多行做成集合来一并处理
第三、你对这些逻辑条件的分析不够充分，能合并为一个条件的结果多次重复出现，也就是重复执行

hyqzzs

版主辛苦，下载学习

excelior

总结的太棒了！

lyb200

总结得不错，有空看看。。。谢谢！

lrh788

Set d = CreateObject("Scripting.Dictionary")
ar = Sheets("订单").Cells(1, 1).CurrentRegion
    For i = 2 To UBound(ar)
        ar(i, 1) = ar(i, 1) & ar(i, 2) & ar(i, 3) & ar(i, 4)
        For j = 5 To UBound(ar, 2)
            d(ar(i, 1) & ar(1, j)) = d(ar(i, 1) & ar(1, j)) & ar(i, j)
        Next j
    Next i
br = Cells(1, 1).CurrentRegion
ReDim br1(1 To UBound(br), 1 To 1)
    For i = 2 To UBound(br)
        br1(i, 1) = br(i, 1) & br(i, 2) & br(i, 3) & br(i, 4)
        For j = 5 To UBound(br, 2)
            br(i, j) = d(br1(i, 1) & br(1, j))
            Cells(i, j) = br(i, j)
        Next j
    Next i
End Sub
运行此代码在数据量几万时还行，但超过10万就慢了，等到最后还时常报错！能否优化代码？减少循环语句？请大师完善下，急用！！！

lysliang2018

真是好帖子啊，给楼主点个大大的赞！

扑腾

刘哥脚印慢慢学习

gulicai2008

和好的，有借鉴和提醒作用