Excel VBA hide/show row code speed.

Question

Not sure how many Excel VBA gurus, besides myself ;-D, that hang around stackoverflow but here's an interesting question.

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.

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

 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

Answer 1

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.

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

Answer 2

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.

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.