Databases
StackOverflow2013
Postsdbo
POHide/show row code speed
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHide/show row code speed
    primarykey
    data
    text
    <p>Goal: Efficiently show/hide rows based on the data in the row.</p> <ol> <li>Create a helper column that determines whether or not a row should be hidden.</li> <li>Have the formula in the helper column return an error or a number.</li> <li>Hide the helper column and write code to execute the hiding/showing.</li> </ol> <p>Question: Which one of the following methods would you expect to be faster? Column B is the helper column and will always be contiguous.</p> <pre><code> 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 &lt;&gt; 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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USGollyJer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHide/show row code speed
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHide/show row code speed
      1. USTheObserver
      1. VTFavorite
    1. COwrong variable on what should be 5th needToShow from bottom
      singulars
      1. POHide/show row code speed
      1. USLance Roberts
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload