Excel – How to create (truly) blank cells from formula so that they get skipped in Ctrl+Arrow

microsoft excelworksheet-function

This question asks about how to use a formula to create truly blank cells so that they won't show up in a chart. The solution is to use the formula NA() which makes the cell take on the value #N/A.

As a common use case, I have a column which is basically a flag, containing a formula IF(*flag_condition*, 1, ""). I then have a SUM() at the top which tells me how many flags are in the column. I would then select an apparently-empty (flag=false) cell near the top of that column and use Ctrl+Down trying to jump to the next nonempty cell of that column, to inspect the values in that row, especially when the flags are sparse and the data is long.

However, Ctrl+Down just goes to the next cell, which appears empty but has the formula.

Using NA() instead of "" (a) makes the cell visibly take on the value #N/A, (b) makes the sum take on the value #N/A, and (c) does not allow CTRL+arrow to skip over that cell.

Therefore, I ask this as a separate question, which is not a duplicate of that.

Is there any solution which overcomes at least issues (c) and possibly (b) of the above?

Per request from @JvdV, here is an example:

enter image description here

The formula in this cell and those below is =IF(MOD(A3,2)=0,1,"").

The intended output is to press Ctrl+Down and jump to B6, instead of B4.

In this case, the flag is not particularly sparse, but in others, it is.

Best Answer

I've found a way to do this, using VBA and Worksheet_Change. I populated a third column, C, that will contain a 1 for even values in column A, and will be empty for odd values in column A. Whenever a value in column A is changed, this code will fire, updating the corresponding value in C. You can then hide column B and use column C to do your Ctrl+Arrow navigation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A3:A999")

'Suppress further events to avoid cascading changes.
Application.EnableEvents = False

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Place your code here.
    If Target.Offset(0, 1).Value = 1 Then
      Target.Offset(0, 2) = 1
    Else
      Target.Offset(0, 2).Clear
    End If

    'Uncomment the next line for troubleshooting.
    'MsgBox "Cell " & Target.Address & " has changed."

End If
'Re-enable further events.
Application.EnableEvents = True
End Sub

This is quick-and-dirty code, so you can likely simplify this somewhat. For example, you could put the If … Mod … logic in the VBA code instead of in a formula in column B, and then you won't need the extra column.

Related Question