Navigating with .End(XlDown) issue

[CountTepes]

I have noticed, that after deleting data in a column then navigating with ctrl-{DownArrow} or even indeed with VBA Range.End(XlDown), the navigation is wrong.

By that I mean that what happens, is that it will ignore empty cells, bypassing them completely. Stop in the middle of a block of data, or stop in the middle of a block of empty cells.

Reversing the navigation, it still stops in the same places.

Any ideas, advice or reasons for this would be very welcome.

 

[jasonb75]

Cells with null strings or non breaking spaces? These usually occur if you copy formulas and paste values where the result of the formula was "", or in data copied from other sources such as web pages.

Regardless of direction, there are 3 behaviour patterns that should occur.

Starting from an empty cell. Stop at the next non empty cell, or the end of the sheet if all remaining cells are empty.
Starting from a non empty cell where the next cell is empty. Stop at the next non empty cell, or the end or the sheet if all remaining cells are empty.
Starting from a non empty cell where the next cell is not empty. Stop at the end of the current range of non empty cells, i.e. the last cell before the next empty cell.

If you have filters applied, then the hidden rows can have an effect.

 

[CountTepes]

I was writing some code on a spreadsheet I was using to test bits of code on.
Before I started. I selected every cell, by clicking on to top left corner where the rulers are and pressed delete.

I then put in some dummy data in column A and tested my code.

It is not the first time I have had that happen. I normally resolve by selecting the rows or columns and deleting them rather than the contents.
Obviously something gets left behind, when a cell has it's contents deleted, which leads Excel to see that there is still something there.

 

[Fluff]

How did you "put in some dummy data", was it copy/paste values?

 

[jasonb75]

As far as I can see, a dirty range left by old data doesn't make any difference.
Neither does adding a comment to an empty cell.

 

[CountTepes]

I put in random characters into a cell, then selected a range, hit Ctrl-D Then copied the range to random places down the column.

It wasn't a new sheet, but one from which I just cleared the old data.

 

[Fluff]

I selected every cell, by clicking on to top left corner where the rulers are and pressed delete.

After select the cells right click & Delete. Does that get rid of the problem?

 

[jasonb75]

Was it a structured table that you deleted the data from?

The boundaries of tables create stop points for ctrl arrow navigation. Clearing contents from a table leaves the basic framework behind.

 

[CountTepes]

No, just flat test data that had been created by a previous code test.
I understand that there must be boundaries there. I just can't work out where they came from, or why they don't disappear when I clear the data.

 

[jasonb75]

I'm out of ideas without seeing the actual file.