Mfcttrf

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:

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.

I'm afraid that because the values in B3, B4 etc are not truely empty cells Excel's Ctrl+Down wont skip to the next cell that has another value than "" because simply "" is some kind of value through formula.

I have tried to overcome that using VBA with a module on the Workbook.Open event. As below:

Private Sub Workbook_Open()

Application.OnKey "^DOWN", "ChangeKey"

End Sub

This tells Excel upon opening to upon pressing Ctrl+Down it needs to call a module called ChangeKey. That particular module could look like:

Sub ChangeKey()

With ThisWorkbook.Sheets("Sheet1")
 Set rng = .Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(.Range("B" & Rows.Count).End(xlUp).Row, ActiveCell.Column))
 For Each cl In rng
 If Len(cl) <> 0 And cl.Row > ActiveCell.Row Then cl.Select: Exit For
 Next cl
End With

End Sub

You should still be able to use SUM on the range now without it giving a #N/A error.

If you do change your mind and you need the #N/A in the cells for chart purposes, you can both:

• 
  

  Use SUMIF to skip the #N/A values:

  
  
  

  =SUMIF(B3:B8,"<>#N/A")
  

  
  


• 
  

  Change the ChangeKey module to skip over #N/A values too

  
  
  

  For Each cl In rng
  If Application.WorksheetFunction.IsNA(cl) = False Then
   If Len(cl) <> 0 And cl.Row > ActiveCell.Row Then cl.Select: Exit For
  End If
  

  
  
  

  Next cl

  
  

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.