<div>
Hello Adriana,<br>I cannot verify that. I see the last row for sheet1 is 1,048,576 as it should be
</div>


<div>
Karen Falandays<br><br>Thanks !
</div>


<div>
<blockquote>
I cannot verify that. I see the last row for sheet1 is 1,048,576 as it should be
</blockquote>
 She's referring to the last used row. The last row with any real data is at around 8000. I believe it's the red border formatting of columns like JA that makes Excel think that the last used row is1,048,576
</div>


<div>
Martin nailed it. &nbsp;The "last row used" is part of the "UsedRange" property of a worksheet. &nbsp;Excel uses both data and formatting such as cell borders and coloring to determine the extent of the "usedrange". &nbsp; Its a common rookie mistake to select an entire column (or row) and apply formatting. &nbsp;Once you've done that, your used range is the last row of the worksheet.
</div>


<div>
I removed the red border formatting of all the &quot;long&quot; columns but Excel <u>still</u> thinks the last used row is 1045876!<br />
<a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570171/29222879.xlsb" target="_blank" class="file-inline" title="29222879.xlsb (19.0 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29222879.xlsb</a>
</div>


29222879.xlsb

<div>
<a href="https://www.experts-exchange.com/members/NameNotAlreadyInUse.html" rel="ugc">Mark Edwards</a>&nbsp;<br><br>Thanks !!
</div>


<div>
the file is still large that you recommend
</div>


<div>
I don't have enough free memory to do it but if someone could copy sheet1 to a new workbook I think that sheet1 in that workbook would be smaller.
</div>


<div>
do you recommend move all the&nbsp; sheets to a new workbook ?
</div>


<div>
Yes. Make sure you copy it from the 29222879.xlsb workbook that I attached.
</div>


<div>
ok im working that &nbsp;now<br><br>
</div>


2021822_4.xlsx.xlsm

2021822_4_1.xlsm

<div>
I was able to delete the vast majority of the empty rows at the end of Sheet1 and the last used row is now 7500 rather than 1,048,576. Surprisingly to me the size of the workbook was only reduced by about 10%, but it should be faster to work with.<br />
<a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570225/29222879a.xlsb" target="_blank" class="file-inline" title="29222879a.xlsb (17.0 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29222879a.xlsb</a>
</div>


29222879a.xlsb

<div>
Great Job !!<br><br>&nbsp;but i miss the navigations buttons and others in the TOP column A to
</div>


ACCOUNTING ASSISTANT

ADRIANA P

<div>
i did<br><br>but need the buttons in the top to work with<br><a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570232/29222879a.xlsb" rel="ugc">29222879a.xlsb</a><br><br>do not use this is not saved
</div>


<div>
here saved<br>after repair&nbsp; &nbsp;<br><a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570234/29222879aa.xlsb" rel="ugc">29222879aa.xlsb</a>
</div>


29222879aa.xlsb

<div>
I'm sorry but I still can't open the repaired file so I guess you'll need to use the 29222829c.xlsb workbook.
</div>


<div>
<div class="content wysiwyg-content fr-view">
<div><div class="content wysiwyg-content fr-view">4NK to make faster and small<br><a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570166/29222829c.xlsb" rel="ugc">29222829c.xlsb</a><br><br><br>Excel thinks that the last used row in Sheet1 is 1,048,548! &nbsp;<br><br>1-Can somebody fix that?"<br>&nbsp;</div></div>
</div>
</div>


29222829c.xlsb


4NK to make faster and small
29222829c.xlsb
Excel thinks that the last used row in Sheet1 is 1,048,548!  
1-Can somebody fix that?"
 

Excel thinks that the last used row in Sheet1 is 1,048,576 ! Can somebody fix that?

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.