When using the xlLastCell in VB it will go further than the last cell?

[mrgillus]

It has to do with the fact that I am putting stuff in and then deleting it. xlLastCell will go to where the end of the last cell where the information used to be that I had deleted was. If that makes since..

I have figured out that if I save the workbook it will update and then xlLastCell will work just fine.. The problem is, that when I use that, it slows down the execution of the macro tremendously..

Any ideas? Is there something else that will update the workbook besides Save?

Here is my code as of now, with the ActiveWorkbook.Save in there.

    Sheets("Pending Approvals").Select
    ActiveWorkbook.Save    
    Sheets("Blank Form").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save    
    Rows("1:65").Select
    Selection.COPY
    Sheets("Pending Approvals").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Cells(ActiveCell.Row + 1, 1).Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=33

Thanks for the help!

[DonkeyOte]

perhaps see: http://www.rondebruin.nl/last.htm

[mrgillus]

Yup well that confirms it. But I didn't really notice anything that would take care of this problem easily.. Looks like most of those codes just tell you where the last cell is (or was if you have deleted)

[telyni]

Why do you save it twice? Why do you select two different sheets, with nothing but a save statement in between? It looks like the first two lines of the code you provided are superfluous, and if the save is the slow part, then getting rid of one out of two save statements would help somewhat.

I know that isn't really an answer to the question you asked, but it sounded like you were going for code optimization due to a speed problem. You probably don't need to select cell A1 before the LastCell statement, either. Select statements slow code down in general.

I hope this helps.

~telyni

[mrgillus]

Thanks, I appreciate the help guys, and yeh I believe your correct.. I don't believe I need to save it twice.. For some reason I was thinking only the sheet it was on would update the last cell during save, not initially thinking that Save would update the entire workbook..