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

Question

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.

Answer 1

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
  

  

Answer 2

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.

Answer 3

The Ctrl+Arrow combo in Excel responds exclusively to breaks in data. Since those cells have a formula, there is never going to be a break in data. The only way around this is to replace the "data" in those cells after the formula is evaluated.

There are 2 basic ways to do this: VBA or non-VBA. Either method can have countless variations. Here are the simplest I have found for both.

VBA:

This would also evaluate the formula itself. It would need to be triggered manually, either through a button or running a macro, each time you would want to evaluate the formula. Where you would place the VBA code would depend on how you wanted to trigger it. I put mine in the lowest-level area needed for access; Sheet1 in this case.

Sub test()

ActiveSheet.Range("B1:B6").Value = Evaluate("=IF(MOD(A1:A6,2)=0,1,"""")")

End Sub

A1:A6 would be where the source being evaluated is located.

B1:B6 would be where the results will be going.

Pro: Extremely fast compared to other calculation methods.

Con: Using Evaluate for complex or array functions can be tricky. There are methods for making the ranges in the code flexible, but I won't get into that here.

Note: Using the Worksheet_Change() event will work. I tend to avoid this solution, however, as the code will run every time the sheet is changed in any way. It can slow things down or interfere with other functions in the sheet.

Non-VBA:

1. Use the formula you need as normal.

2. Select the results (starting at the top and pressing Ctrl+Shift+Down is the easiest method).

3. Copy

4. Paste Values Only (Do NOT change your selection!)

5. Do a Text to Columns, Fixed Width, with no breaks selected (Again, making sure you do NOT change your selection)

Pro: No code to maintain. Good skill to have.

Con: Has to be done manually every time. Pastes over your formula, making you retype it every time you need it.