CTRL(+SHIFT)+END includes deleted rows at bottom

[Tana Lee]

Aloha...

Assume a range of cells.

Some rows contain data. Some do not.

ACTION:

- A macro is run to delete the 'empty' rows. The rows that follow move up.

OR

- One or more rows is manually deleted from the bottom of the range.

RESULT: CTRL(+SHIFT)+END still includes the now blank original rows at the bottom of the range.

QUESTION 1: Why?

QUESTION 2: How do you get it to stop doing that?


Mahalo,
Tana-Lee

 

[Von Pookie]

The only way I know is to close the file and open it again. Kind of like "refreshing" a webpage after you make changes.

Perhaps there's a way to get it to think that's been done with VBA? I don't know. The only other idea I can think of is maybe find a code that simply selects rows with actual data in them instead of using ctrl/shift/end.

 

[eliW]

hi Tana-Lee,

maybe this can help:

http://www.mrexcel.com/board2/viewtopic.php?t=34273&highlight=end

Eli

 

[Tana Lee]

Aloha...

Thanks, Eli!

Ivan's post, in part:

---------------------
Once you have enter data on a Worksheet Excel needs to remember or recall this info ie. the cells data and properties etc. This is so that it can Undo an operation...To reset the new range just save your workbook OR
Run this code;

Application.UsedRange
---------------------

I ran a macro to delete blank rows on a tiny range of months with every other row blank. The last line of the macro I tried using "Application.UsedRange" but it didn't work. So I changed it to "ActiveSheet.UsedRange".

Now CTRL(+SHIFT)+END works fine.

So, thanks Eli, and thanks Ivan.

Tana-Lee

 

[alhowarth]

Aloha...

Run this code;

Application.UsedRange
---------------------

..................but it didn't work. So I changed it to "ActiveSheet.UsedRange".

Now CTRL(+SHIFT)+END works fine.

So, thanks Eli, and thanks Ivan.

Tana-Lee

Can someone tell me how you "Run Code"?? Do you have to create a macro in the spreadsheet? I would like to be able to perform this action to any spreadsheet that I open. Is this possible?

 

[vds1]

Yes you should have macro in one of the open workbooks... and then press ALT+F8 to run macros...

 

[alhowarth]

Thanks. Unfortunately, the code didn't work... Although this post is 11 years old, so I'm guessing it's a version issue. I'll keep looking.

 

[joeu2004]

Thanks. Unfortunately, the code didn't work... Although this post is 11 years old, so I'm guessing it's a version issue.

No, ActiveSheet.UsedRange is the best way to "reset" ctrl+End to include just the "used" cells.

However, there are some conditions that cause Excel to "use" cells that are empty. For example:

1. Start with a new worksheet.
2. Select A1:F7 (i.e. 7 rows and columns).
3. Assign a standard format, e.g. Number or even General.
4. Delete row 6: click on the row number on the left, then right-click and click on Delete.
5. Press ctrl+End. Note that F7 is selected.
6. Execute ActiveSheet.UsedRange in VBA.
7. In Excel, press ctrl+End. Note that F6 is selected.
8. Save and close the file.
9. Reopen the file.
10. Press ctrl+End. Note that F6 is selected.

PS: You do not need to have a macro in order to execute ActiveSheet.UsedRange. It is sufficient to execute the statement in the Immediate Window. In Excel, press alt+F11 to open the VBA window. Then in VBA, press ctrl+G to open the Immediate Window. Type ActiveSheet.UsedRange in the Immediate Window. Or better: Print ActiveSheet.UsedRange.Address to see the actual range.