vba 隐藏/显示行代码速度

Hide/show row code speed

提问by GollyJer

Goal: Efficiently show/hide rows based on the data in the row.

目标：根据行中的数据有效地显示/隐藏行。

1. Create a helper column that determines whether or not a row should be hidden.
2. Have the formula in the helper column return an error or a number.
3. Hide the helper column and write code to execute the hiding/showing.

1. 创建一个辅助列，用于确定是否应隐藏行。
2. 让辅助列中的公式返回错误或数字。
3. 隐藏辅助列并编写代码来执行隐藏/显示。

Question: Which one of the following methods would you expect to be faster? Column B is the helper column and will always be contiguous.

问题：您希望以下哪种方法更快？B 列是辅助列，并且始终是连续的。

 Sub SetRowVisibility1()

  Dim rowsToCheck As Range
  With ActiveSheet
    Set rowsToCheck = .Range(Range("B7"), Range("B7").End(xlDown))
  End With

  Dim needToShow As Range, needToShow_Showing As Range
  Dim needToHide As Range, needToHide_Showing As Range

  Set needToShow = rowsToCheck.SpecialCells(xlCellTypeFormulas, xlNumbers)
  Set needToHide = rowsToCheck.SpecialCells(xlCellTypeFormulas, xlErrors)

  On Error Resume Next
  Set needToShow_Showing = needToShow.Offset(0, 1).SpecialCells(xlCellTypeVisible)
  Set needToHide_Showing = needToHide.Offset(0, 1).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0

  If Not needToHide_Showing Is Nothing Then
    needToHide_Showing.EntireRow.Hidden = True
  End If
  If Not needToShow Is Nothing Then
    If needToShow.Count <> needToShow_Showing.Count Then
      needToShow.EntireRow.Hidden = False
    End If
  End If

End Sub


Sub SetRowVisibility2()

  Dim rowsToCheck As Range
  With ActiveSheet
    Set rowsToCheck = .Range(Range("B7"), Range("B7").End(xlDown))
  End With

  Dim needToShow As Range, needToHide As Range
  Dim cell As Range
  For Each cell In rowsToCheck

    If IsError(cell.Value) And (cell.EntireRow.Hidden = False) Then
      If needToHide Is Nothing Then
        Set needToHide = cell
      Else
        Set needToHide = Union(needToHide, cell)
      End If
    End If

    If Not IsError(cell.Value) And (cell.EntireRow.Hidden = True) Then
      If needToShow Is Nothing Then
        Set needToShow = cell
      Else
        Set needToShow = Union(needToShow, cell)
      End If
    End If

  Next cell


  If Not needToHide Is Nothing Then needToHide.EntireRow.Hidden = True
  If Not needToShow Is Nothing Then needToShow.EntireRow.Hidden = False

End Sub

回答by SpyJournal

there is a different way and that is to use th auto filter feature - after all VBA has an A in it - use the features of the application wherever possible so this bit of code is pretty short and sweet - assumes that the data is a contiguous block in columns a and b and assumes no other error handling in play. the resume next line allows for the filter to be already turned on.

有一种不同的方法，那就是使用自动过滤器功能 - 毕竟 VBA 中有一个 A - 尽可能使用应用程序的功能，所以这段代码非常简短和甜蜜 - 假设数据是连续的阻止 a 和 b 列，并假设没有其他错误处理。简历下一行允许过滤器已经打开。

Sub showHideRange()
Dim testrange
    testrange = Range("A1").CurrentRegion.Address
    On Error Resume Next
    testrange.AutoFilter
    ActiveSheet.Range(testrange).AutoFilter Field:=2, Criteria1:="show"
End Sub

回答by Dave DuPlantis

If you do not wish to show the user what's happening, would it not be better to perform the calculation in VBA itself, rather than in a hidden column? Granted, that would seem to lock you into option 2, which I suspect is the slower option ... most of my VBA experience is in older versions of Excel, so I've not had the pleasure of working with some of the newer features, and the tasks I've done that involved processing rows of data were done row-by-row.

如果您不想向用户显示发生了什么，在 VBA 本身而不是在隐藏列中执行计算不是更好吗？当然，这似乎会将您锁定在选项 2 中，我怀疑这是较慢的选项......我的大部分 VBA 经验都在旧版本的 Excel 中，所以我没有愉快地使用一些较新的功能，我所做的涉及处理数据行的任务是逐行完成的。

I guess one possible issue with the first sub is that if there is a problem with the worksheet or the values you're using to determine hiding/showing, the process will fail. If you check row-by-row and there is a row that causes problems, you could skip over that row and process the other ones correctly.

我想第一个 sub 的一个可能问题是，如果工作表或您用来确定隐藏/显示的值有问题，则该过程将失败。如果您逐行检查并且有一行导致问题，您可以跳过该行并正确处理其他行。