Last Cell increases on Deletion of Rows

[Firann]

Greetings all.

I have created a worksheet that goes to Column BA and up to Row 625. Every other row and column after these two is hidden. The last cell indicated after using Goto function is correctly states as BA625. In this sheet I have various tests that coworkers need to perform for their job and at certain areas I have a comment section for them to add comments and give them the option to add additional rows to the comments or delete unnecessary ones if they insert more than required.

Now if they add two rows to 300 and 301, my last cell correctly goes to BA627 since two rows were inserted before it. However, the problem arises when you delete fro example row 300, instead of deleting the row and only making my last row 626 and last cell BA626, it also adds / unhides a row after my last row and changes the last cell to this new row.

Is there a way to force it to NOT add rows / unhide rows after my designate last row? Even adding a VBA code to force last cell to the one I want and hide any rows after it would do the job. My worksheet has increased in size tremendously from the 1.4Mb it is on creation to 2.5Mb due to all these extra rows being added, and while I can control it during the development stage once its released within the company I can not go to every single form users create to adjust it.

Thanks for any input.

 

[JLGWhiz]

When you have the rows below a certain point hidden and then insert a row or rows above that point, Excel will unhide the equivalent number of rows at the bottom of the visible rows because the visible properties of the rows were not changed by inserting rows. The same holds true for deleting rows. The rows will be deletetd but since an excel sheet automatically replaces deleted rows so that the number of rows on a sheet is always the same, it does not delete rows from the visisble section because their visible properties did not change. I don't know that any of that has to do with your file size directly, other than stored memory which might be attached to the file. But the only way you can reduce the number of visible rows is to change the visible properties to hidden. You can do that with the following snippet palced in the worksheet code module which will run on every change made to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A:A").SpecialCells(xlCellTypeVisible).Count > 625 Then
    Range("A626", Cells(Rows.Count, 1)).EntireRow.Hidden = True
End If
End Sub

 

[mole999]

as a formula > =SUMPRODUCT(MAX(($S6:$S2000<>"")*(ROW(S6:S2000)))) will track the physical row in column S