How can I reset the “LastCell” in Excel?

[LonnieCurrier]

How can I reset the “LastCell” value in Excel?

I need a macro that selects the correct print area after deleting several rows and columns.

My original spreadsheet has 2000 rows and 26 columns. The “LastCell” with data is Z2000. Using the Excel keyboard shortcut “Ctrl-End” successfully selects the “LastCell” which is cell Z2000. After deleting rows and columns the spreadsheet is now 1000 rows and 13 columns. The “LastCell” should now be cell M1000, but Ctrl-End still selects cell Z2000 instead of M1000 and the print area is still 2000 rows and 26 columns, printing a lot of empty pages.

[#2 · Accepted solution]

@LonnieCurrier  Did you save the workbook after deleting the empty rows and columns? Excel remembers the previous "Last cell" until you do so.

[#3]

@Riny_van_Eekelen  Thanks for the information!  No, the macro that deletes rows and columns was not saving the file before trying to use the "LastCell" function.  The documents are received by e-mail, so they are read-only when opened.  Now I need to figure out how to Save-As in the macro so that it will save to a location that the user would have permissions to.  I tested Save-As in the macro, and it records my specific username in the path:

        ActiveWorkbook.SAVEAS Filename:= _
        "C:\Users\LonnieCurrier\Documents\FILENAME.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

Is there a variable/function that I can use in place of my username so that Save-As will work for other users?

[#4]

@LonnieCurrier You can declare three variables e.g. Dim UserName, SaveToPath, FileName as String

and then build the entire path, something like:

SaveToPath = "C:\Users\" & UserName & "\Documents\" & FileName

Then you SaveAs code could become:

        ActiveWorkbook.SAVEAS Filename:= SaveToPath, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

Now, I didn't test this and hope I didn't make any typos. But, in case I did, I guess you can figure it out by yourself and make it work.

[#5]

@Riny_van_EekelenThanks again!  I got this to work:

    ActiveWorkbook.SAVEAS Filename:= _
    Environ("UserProfile") & "\Desktop\TemporaryFileName.xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

[#6]

@LonnieCurrier Great!!