在VBA Excel 2003中迭代for循环为800行花费太多的时间来获得所需的输出?如何克服这个? [英] In VBA Excel 2003 Iterating in for loop for 800 rows taking too much time to get the desired output?How to overcome this?
问题描述
对于i = 0 To WSTreatmentOutComes.Range(F14:F813)。Rows.Count - 1
如果WSTreatmentOutComes.Range(F& 14 + i).Value =或WSTreatmentOutComes.Range & 14 + i).Value =0然后
对于j = 0到9
WSTreatmentOutComes.Range(F& 14 + i + j).Rows.Hidden = True
下一步j
Else
对于k = 0到9
如果WSTreatmentOutComes.Range(G& 14 + i + k).Value =或WSTreatmentOutComes.Range G& 14 + i + k).Value =0然后
WSTreatmentOutComes.Range(F& 14 + i + k).Rows.Hidden = True
End If
下一个k
结束如果
i = i + 9
下一个i
列A中的
这样做一个公式测试:
范围(A14:A813)。formula == IF(AND(F:F =,G:G = 0),NA(),)
然后使用这样的代码隐藏或取消隐藏:
'首先,取消隐藏所有行:
WSTreatmentOutComes.rows.hidden = false
'
'现在隐藏那里有一个NA公式中的#错误:
WSTreatmentOutComes.Range(A14:A813)。SpecialCells(xlCellTypeFormulas,xlErrors).EntireRow.Hidden = True
最后,清理列A:
范围(A14:A813 ).clearcontents
你去,立即隐藏他们
更多关于此方法/技术的 StackOverFlow :how-to-delete-multiple-rows-without-a-loop-in-excel-vba
Please suggest the solution to iterate 800 rows in Excel 2003 for hiding and unhiding the rows.
For i = 0 To WSTreatmentOutComes.Range("F14:F813").Rows.Count - 1
If WSTreatmentOutComes.Range("F" & 14 + i).Value = "" Or WSTreatmentOutComes.Range("F" & 14 + i).Value = "0" Then
For j = 0 To 9
WSTreatmentOutComes.Range("F" & 14 + i + j).Rows.Hidden = True
Next j
Else
For k = 0 To 9
If WSTreatmentOutComes.Range("G" & 14 + i + k).Value = "" Or WSTreatmentOutComes.Range("G" & 14 + i + k).Value = "0" Then
WSTreatmentOutComes.Range("F" & 14 + i + k).Rows.Hidden = True
End If
Next k
End If
i = i + 9
Next i
in column A put a formula test like this:
Range("A14:A813").formula="=IF(AND(F:F="""",G:G=0),NA(),"""")"
then use code like this to hide or unhide:
' first, unhide all rows:
WSTreatmentOutComes.rows.hidden=false
'
'now hide the ones where there is an NA# error from the formula:
WSTreatmentOutComes.Range("A14:A813").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
finally, clean up column A:
Range("A14:A813").clearcontents
there you go, hides them instantly
more on this method/technique at StackOverFlow: how-to-delete-multiple-rows-without-a-loop-in-excel-vba
这篇关于在VBA Excel 2003中迭代for循环为800行花费太多的时间来获得所需的输出?如何克服这个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!