Placeholding blank cells

M3Drivers · Mar 21st, 2002, 03:48 PM

I have a spreadsheet which uses xldown to determine the end of a range of a dynamic column. The problem is that when cells are populated but then deleted, it is like they are still there. If you hit CNTRL+END the cursor goes to the last cell of the just-deleted area instead of shifting up to where the new data ends.

Anybody have any ideas as to why this happens and how to make it stop or reset the end of the range? Thanks.

Mudface · Mar 21st, 2002, 03:51 PM

I think you need to save the file first.

Jack in the UK · Mar 21st, 2002, 03:55 PM

Hi

Very common this one, sounds like you have deleted the text or numbers from the cells . ment to or accident dont matter this is what i refer to as cleaned up cells thay are still dirty cells that is blank but excel still sees them and if they had text in them still.

FIX:

Select all the empty cells and right click, select clear contence and click, this forces exzcel to didtroy data unconditionally so they are empty cells,

Now try....

Mudface · Mar 21st, 2002, 04:00 PM

Sorry, Jack, this and deleting the columns/ rows, shifting them left/up etc only works if the file is then saved. Excel still thinks they're dirty until you save.

M3Drivers · Mar 21st, 2002, 04:01 PM

Thanks guys. That works.

Mark W. · Mar 21st, 2002, 04:08 PM

You can also reset the worksheet extent by using ASAP utilities' (see www.asap-utilities.com) Sheets | Reset Excel's last cell menu command.

This utility is freeware!

[ This Message was edited by: Mark W. on 2002-03-21 15:55 ]

Mar 21st, 2002, 03:55 PM	#3
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Hi Very common this one, sounds like you have deleted the text or numbers from the cells . ment to or accident dont matter this is what i refer to as cleaned up cells thay are still dirty cells that is blank but excel still sees them and if they had text in them still. FIX: Select all the empty cells and right click, select clear contence and click, this forces exzcel to didtroy data unconditionally so they are empty cells, Now try.... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 03:48 PM	#1
M3Drivers New Member Join Date: Mar 2002 Posts: 4	I have a spreadsheet which uses xldown to determine the end of a range of a dynamic column. The problem is that when cells are populated but then deleted, it is like they are still there. If you hit CNTRL+END the cursor goes to the last cell of the just-deleted area instead of shifting up to where the new data ends. Anybody have any ideas as to why this happens and how to make it stop or reset the end of the range? Thanks.

Mar 21st, 2002, 03:51 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	I think you need to save the file first.

Mar 21st, 2002, 04:00 PM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Sorry, Jack, this and deleting the columns/ rows, shifting them left/up etc only works if the file is then saved. Excel still thinks they're dirty until you save.

Mar 21st, 2002, 04:01 PM	#5
M3Drivers New Member Join Date: Mar 2002 Posts: 4	Thanks guys. That works.

Mar 21st, 2002, 04:08 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	You can also reset the worksheet extent by using ASAP utilities' (see www.asap-utilities.com) Sheets \| Reset Excel's last cell menu command. This utility is freeware! [ This Message was edited by: Mark W. on 2002-03-21 15:55 ]