excel - 使用 Do While 优化 VBA 宏
问题描述
我有一个包含很多工作表的 excel 文件,我想运行一个宏,该宏将根据该范围顶部的值隐藏一系列行。我的宏可以工作,但是由于我有很多工作表,所以要花很长时间才能运行...
有人可以帮我优化它,因为我可能做了一些非正统的事情......
Sub MasquerPrix()
Dim RowNum As Long
Dim StartRow As Long
Dim ColNum As Long
Columns("D:H").Select
Range("F1").Activate
Selection.EntireColumn.Hidden = False
Columns("E:F").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
StartRow = 1
RowNum = 1
ColNum = 2
Do While Cells(RowNum, ColNum).Value <> "Prix Total (Public HT)"
If Cells(RowNum, ColNum).Value <> "Prix Total (Public HT)" Then
Rows(RowNum).Resize(12).EntireRow.Hidden = True
Rows(StartRow & ":" & (RowNum)).EntireRow.Hidden = False
End If
RowNum = RowNum + 1
Loop
End Sub
太感谢了 !
解决方案
没有原始数据并且不了解其处理的逻辑,草拟了一个使用Application.Match()
而不是Do ... Loop
Sub MasquerPrix() 'processes ActiveSheet
Const STR_TO_MATCH = "Prix Total (Public HT)", COL_NUM = 2
Dim RowNum As Long, StartRow As Long
Application.ScreenUpdating = False
Columns("D:H").Hidden = False
Columns("E:F").Hidden = True
StartRow = 1
RowNum = Application.Match(STR_TO_MATCH, Columns(COL_NUM), 0)
If IsNumeric(RowNum) Then
Rows(RowNum).Resize(12).EntireRow.Hidden = True
Rows(StartRow & ":" & RowNum).EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub
推荐阅读
- r - 如何根据特定条件提取字符串
- c# - C# 字典过滤 (LINQ) 和列表扩展方法过滤值并获取密钥
- excel - excel VBA中的计算
- python - 对列表列表中的值进行排序
- java - 区域的 LocalDateTime 不显示该区域的一天中的小时
- javascript - 计算数组中的单词而不是字符
- java - 左移丢失的信息是否存储在 Java 中的某个地方?
- r - How can I retrieve the variances in the ICC formula in R?
- python - Parsing Google Scholar results with Python and BeautifulSoup
- apache-kafka - Not able to read event from single topic that has single partition through multiple consumers