Clear All not working correctly

[Matt_O]

Hi Excel Experts,

First, I haven't been on this site in a long time and kudos to whomever revamped the site. It looks great.

Second, thanks in advance to anyone who responds.

I have come across a situation in two separate workbooks from different authors where I check for 'Last Cell' by manually checking via F5 > 'Special' > and selecting 'Last Cell'.

In the most recent workbook I received, there is data in (A1:D8), yet the last cell is (D:965). I selected (A9: D965) and then selected on the Home Ribbon > Editing > Clear > Clear All. Then I saved the file.

When I manually check the sheet again for last cell it ends up back on (D:965). I then highlighted a couple hundred rows (700-965) and deleted the rows. When I manually checked for the last cell it still showed (D:965). Eventually I was able to delete a couple hundred rows and the 'last cell' was on found on the (D:700). I repeated the selection and deleting process for another couple hundred rows and the 'last cell' was found back on row 700. I've been playing around with 'deleting' and 'clearing all' 'and continue to get inconsistent results.

I started using the F5 process to first search for invisible objects because some workbooks were 2 MB large yet contained only a few rows of data. Then I was copying over numerous empty rows when using Python and openxl with part of the code looking like "x = source_ws.max_row". It appears that Python/openxl equates 'max_row' to Excel's 'Last cell'. To troubleshoot this issue I started using the F5 Last cell option on the source workbook and stumbled into this puzzle.

When using VBA ... aLastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row... I get the intended result of Row 8 being the last row containing data.

So that was a longwinded way to ask, does anyone know if the F5 > 'Special' > and selecting 'Last Cell' process is known to be buggy? Or is my experience an outlier?

I'm on Windows 10, Office 365, and confused.

Matt

 

[Zot]

This probably related to your question?

Locate and reset the last cell on a worksheet - Microsoft Support

When you save a workbook, Excel stores only the part of each worksheet that contains data or formatting. Empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data.

support.microsoft.com

 

[johnnyL]

You might want to check this out also --> Reducing workbook file size

 

[Matt_O]

Thanks for replying Zot and johnnyL.

After reading the article Zot linked to, it seems I still have a bug. Now my last cell is only on row 24 but it doesn't matter if I select A9:D24 and 'clear all' or delete the rows. The next time I open the file and CTRL + End the last cell is still D:24. So strange.

I'll have to set aside some time to digest and try out johnnyL's code.

Thanks again.

Matt