Clever Geek Handbook

Excel VBA hide / show speed of line of code

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

Objective: Effectively show / hide rows based on the data in the row.

  • Create a helper column that specifies if the row should be hidden.
  • Have a formula in the column helper that returns an error or number.
  • Hide helper column and write code to do hide / show.

Question: Which of the following methods do you expect faster? Column B is a construction 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
+1


source to share


2 answers


there is another way that is to use the autofilter function - after VBA has A in it - use the application capabilities where possible so this bit of code is pretty short and sweet - assumes the data is a contiguous block in columns a and b, and do not accept any other error handling in the game. the next line of resume allows you to enable the filter.



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
+1


source


If you don't want to show the user what's going on, wouldn't it be better to do the calculations in VBA rather than on a hidden column? Of course this seems to block you in option 2, which I suspect is the slower option ... most of my VBA experience is in older versions of Excel, so I haven't had the pleasure of working with some of the newer features. and the tasks that I did that involved processing rows of data were done row by row.



I guess one of the possible problems with the first sub is that if there is a problem with the sheet or the values ​​you are using to define hide / show, the process will fail. If you are checking line by line and there is a line that is causing problems, you can skip that line and handle the others correctly.

0


source






More articles:


All Articles