Navigation Strg+Arrowkeys

[Daniel Arbeit]

Hello,



with Strg+Arrowkey you can jump to the start or the end where data is in cells.

When you insert a Formular this doesn't work anymore - so i thought it's the Formular and tried to kopy the whole column and inserted it with only values (now there is no Formular in the cells -> like it was written manually). Suprisengly it still did not work.

Maybe you have a hint.



Thanks,
Daniel

[BobBridges]

"Strg" must be short for something in German, Daniel. It sounds like you're describing what happens with <Alt-End,<arrow>>; if the active cell contains data, it moves in the direction of the arrow until it comes to an empty cell, and vice versa.

If that's what you're talking about, you'll have to tell more than "doesn't work". What does it do...exactly?

[Daniel Arbeit]

Sorry "Strg" means "Ctrl".
E.g.:

The cell A1 is selected i press Ctrl+Arrowkeydown and it jumps to A2 (the last cell where there is still data without interrupt by empty cells). Next Ctrl+Arrowkeydown it jumps to A4 (begin of data) then A6 (end of data).

In the colomn C and D this doesn't work. It jumps from C1/D1 to C9/D9 without stopping at the data inbetween (it should stop at C4/D4).

From Microsoft:
"CTRL+ARROW KEY moves to the edge of the current data region in a worksheet."
https://support.office.com/en-us/art...9-9b7213f0040f

[xor]

From the picture in your original post I read it as you have formulas in C2:C9. If that is correct Excel doesn't stop before C9 because there are no real empty cells between C2 and C9.

[Daniel Arbeit]

Xor thank's for the hint, got it now. The cells are not truely empty and there is no way to do this by a formular.

You can do it by VBA however:

Sub Empty_Cells()
Dim SomeRange As Range
Set SomeRange = Range("D29") 'COMMENT this means D2 : D9 without spaces, forum makes the smiley xD
For Each Cell In SomeRange
If (Cell.Value = "") Then Cell.ClearContents
Next
End Sub

[xor]

Not by formula. If you have your formula in C9 and copy and paste as values in D9 then try in an empty cell to enter =ISBLANK(D9)

You may be able to do what you want by means of VBA.

[Daniel Arbeit]

Yes you are right ISBLANK Returns false meaning it's not truely blank/empty for Excel. This only seems to work by Object.ClearContents by VBA.