Forum Discussion
How can I reset the “LastCell” in Excel?
LonnieCurrier Did you save the workbook after deleting the empty rows and columns? Excel remembers the previous "Last cell" until you do so.
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?
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.
Riny_van_EekelenThanks again! I got this to work:
ActiveWorkbook.SAVEAS Filename:= _
Environ("UserProfile") & "\Desktop\TemporaryFileName.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=FalseLonnieCurrier Great!!