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

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.

I'm afraid that because the values in B3, B4 etc are not truly empty cells Excel's Ctrl+Down won't 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