The correct cell range of the data

[slack7639]

If you toggle . . . Ctrl+Home / Ctrl+End . . . Ctrl+Home / Ctrl+End

Or you use this command:
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

. . . I find that the correct cell range has been thrown off sometimes, somehow, in the bottom right, the "End"

. . . When you do Ctrl+End, it does not take you to the actual bottom, right cell of the data-table, but to a cell one or more rows down

To fix it, you have to:
1.) delete all cells from the bottom of the data, to the bottom of the xlsm (Row 1,048,576)
2.) delete all cells from the right of the data, to the right of the xlsm (Column XFD)
3.) go to cell A1, and Save the xlsm
Then Excel "knows" the correct data-table range of cells

What causes the correct cell range to be thrown off?

Is there a better, faster, easier way to clear this up?
Is there some command that could be used?
Or, is human decision always required?

 

[Joe4]

Can you show us a sample of your data, and explain (in plain English) in relation to this sample, exactly what you are trying to do?
There may be other better ways of doing what you want, but it is important for us to see exactly what your data structure looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[slack7639]

Hi!

It's nothing fancy, just my bookmarks. My data is currently in cells A3:AC31926

I use a macro to sort them, and somehow, the "End" becomes one row lower.

I don't think the macro could be the reason, though, because, I've seen this on other spreadsheets, with no macros.

Something happens, where Ctrl+End goes beyond your data. I think it's a common occurrence.

I'm just wondering if there is an easier way to clear it up, than what I know to do.

 

[Joe4]

Something happens, where Ctrl+End goes beyond your data. I think it's a common occurrence.

It usually means that either you have formatted some cells in that last row, or have a space or something.
Sometimes if you delete a row, the "end" does not get updated until you re-save the file.

I'm just wondering if there is an easier way to clear it up, than what I know to do.

One common way I use is to identify some column that will ALWAYS have an entry in it for any data row.
Let's say that column A is always populated for every row with data.
Then you can find the last row with data like this:

Dim lr as Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

 

[slack7639]

Thanks!

 

[Joe4]

You are welcome.