CTRL+End is on row 133 at the end of a macro, but is on row 17 (last row of data) after closing and re-opening the workbook. Why?

[VBAProIWish]

Hello All,

I just got some code from some great folks earlier today and I thought my problem was fixed until I re-ran the code again.

Here's what's going on....

I have a sub (called mod0) that calls 3 modules; mod1, mod2, mod3.

When I run the full routine, calling all three modules, CTRL+End brings me to row 133, when in fact my last row of data is row 17.

However, if I set a breakpoint and stop BEFORE mod3 runs, save the workbook, close it, re-open it and run mod 3 by itself, CTRL+End brings me to a cell in row 17; just like it should.

This is so frustrating. Why would closing and re-opening the workbook change the last cell with "something" in it?

Why can't I just call mods 1,2 & 3 and have CTRL+End go to a cell in row 17 without any interruptions?

Any thoughts on this would really be appreciated.

Thanks

 

[Jonmo1]

It's a weird little quirk (may be a bug, but it's known and documented).

I assume your macro is deleting rows.
Basically it remembers what the lastcell 'WAS' before rows got deleted.
Saving the book resets that property.

Another way is to reference UsedRange in your code.
Don't even need to actually 'Do' anything with it, just put the reference in there somewhere.
That will also reset the lastcell.

So just add this where you put your breakpoint

ActiveSheet.UsedRange

 

[VBAProIWish]

Wowwwwwwwwwwwwwwwwwwwwwwwwwwww,

I think that is the single best line of code EVER CREATED!

Thanks soooooo muchhhhhh!

 

[Jonmo1]

You're welcome