How to reset xlCellsTypeLastCell when clearing a worksheet?

I want to clear a worksheet without deleting it (so formulas on other sheets referring to it don't dereference). Using a command like

Please Login or Register  to view this content.

looks like it clears everything, but a subsequent

Please Login or Register  to view this content.

still moves to the previous last cell, not A1. When I later add data to the sheet that doesn't fill the previous area the xlCellTypeLastCell designation is useless. The same problem occurs when using the Ctrl+End keyboard shortcut. I haven't found any deletion method that doesn't have this quirk.

Suggestions?

Henry

Can you post a sample of the code you are using. I have never used xlCellTypeLastCell. Why dont you use a row.count reference?

Originally Posted by fredlo2010

Can you post a sample of the code you are using. I have never used xlCellTypeLastCell.

Here's an example:

Please Login or Register  to view this content.

The active cell is then the empty cell C10, not A1.

Originally Posted by fredlo2010

I have never used xlCellTypeLastCell. Why dont you use a row.count reference?

Rows.Count requires a selection of the desired range, but the point is that I am trying to determine the range. Apparently, if used without a range it exhibits the same problem; it goes to the last row of the sheet before it was cleared, if that has a higher row number than the current data in the sheet occupy. The problem exists with any Ctrl+ Arrow Key combination (or the xl equivalents in code), not just xlCellTypeLastCell (or its alias, xlLastCell).

I am currently workong around the problem by selecting the first cell in a column that I know has contiguous data in all its rows, and using Application.WorksheetFunctions.COUNT (or COUNTA) to find the last row.

Thanks, but I'm still trying for a better solution, in case I don't have a solid column I can count on. (Pardon the pun.)

Henry

maybe something like

Please Login or Register  to view this content.

I cannot tell because I am not looking at you code. But you should not be using Select for anything there is no need.

Originally Posted by nilem

maybe something like

Please Login or Register  to view this content.

That does it! Apparently finding the UsedRange resets the target of the ctrl + Arrow Keys and End operations.


THANKS VERY MUCH

Henry