Last Row is Always Row 1048576

[richh]

Hi all,

I'm running into an issue that I can't seem to solve. My sheets are going to the absolute last row and are drastically impacting the file size of my workbook. Excel seems to think that my sheet goes to the very last row, even after clearing contents and deleting rows. This is turning a sheet that is less than 50 KB into a 5MB+ monstrosity. Given that I have 12 sheets (one for each month) with the same issue, I've just realized that my workbook is close to 70MB just because of this issue.

Any idea how I can resolve this issue?

 

[Joe4]

Are you applying any sort of formulas or formatting to the entire sheet or entire columns?
If you can limit it to just the section you are using, it might prevent that issue.

 

[richh]

I have Freeze Panes turned on to freeze the first two rows and columns, but that's the only sheet-wide setting I have on. I notice that one of the cells in that row has a border, but even after removing the borders from the row, it still jumps all the way down. I've even deleted the row and the single border comes back each time.

 

[Joe4]

What is you try deleting all rows from your last row of actual data down to the last possible row, and then save the file?
Then if you hit CTRL-END, does it still jump all the way down to the end?

By the way, how are these sheets being populated with data? Where is the data coming from?

 

[richh]

Yes, I'm deleting all the rows after the last row with valid data on it. After hitting CTRL-END, Excel takes me to the very last row. The column it takes me to is correct, but the row is VERY far from where it should be.

The data is being entered manually. There is, however, a short VBA script I can use to enter in data without having to do fully manual entry. My workbook is a basic attendance log. Sheets are by month, columns are the dates of the month, rows are the employees.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim dateSearch As String

Set ws = Worksheets(Me.dateMonthYear.Value) //sets the ws to the month selected

// sets the date
If dateMonthYear.Value = "Jan-17" Then
dateSearch = "1/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Feb-17" Then
dateSearch = "2/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Mar-17" Then
dateSearch = "3/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Apr-17" Then
dateSearch = "4/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "May-17" Then
dateSearch = "5/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Jun-17" Then
dateSearch = "6/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Jul-17" Then
dateSearch = "7/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Aug-17" Then
dateSearch = "8/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Sep-17" Then
dateSearch = "9/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Oct-17" Then
dateSearch = "10/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Nov-17" Then
dateSearch = "11/" & Me.dateDay.Value & "/2017"
ElseIf dateMonthYear.Value = "Dec-17" Then
dateSearch = "12/" & Me.dateDay.Value & "/2017"
End If

For i = 3 To 83
If Me.sName.Value = ws.Cells(i, 1).Value Then

//Logs the data entered on the form into the ws
For j = 3 To 64
If dateSearch = ws.Cells(1, j).Value Then
ws.Cells(i, j + 1).Value = Me.hrsIn.Value
ws.Cells(i + 1, j).Value = Me.abCode.Value
ws.Cells(i + 1, j + 1).Value = Me.hrsOut.Value
ws.Cells(i + 2, j).Value = Me.atComments.Value

//Adds color code to cells depending on the code
If Me.abCode.Value = "V" Or Me.abCode.Value = "PH" Or Me.abCode.Value = "HC" Or Me.abCode.Value = "PLP" Or Me.abCode.Value = "PDD" Or Me.abCode.Value = "AL" Then
ws.Cells(i, j + 1).Interior.Color = RGB(204, 192, 218)
ws.Cells(i + 1, j).Interior.Color = RGB(204, 192, 218)
ws.Cells(i + 1, j + 1).Interior.Color = RGB(204, 192, 218)
ws.Cells(i + 2, j).Interior.Color = RGB(204, 192, 218)
ElseIf Me.abCode.Value = "BL" Or Me.abCode.Value = "FS" Or Me.abCode.Value = "S" Then
ws.Cells(i, j + 1).Interior.Color = RGB(216, 228, 188)
ws.Cells(i + 1, j).Interior.Color = RGB(216, 228, 188)
ws.Cells(i + 1, j + 1).Interior.Color = RGB(216, 228, 188)
ws.Cells(i + 2, j).Interior.Color = RGB(216, 228, 188)
ElseIf Me.abCode.Value = "CTO" Or Me.abCode.Value = "FH" Or Me.abCode.Value = "ITO" Or Me.abCode.Value = "JD" Or Me.abCode.Value = "OSB" Then
ws.Cells(i, j + 1).Interior.Color = RGB(255, 255, 153)
ws.Cells(i + 1, j).Interior.Color = RGB(255, 255, 153)
ws.Cells(i + 1, j + 1).Interior.Color = RGB(255, 255, 153)
ws.Cells(i + 2, j).Interior.Color = RGB(255, 255, 153)
Else
ws.Cells(i, j + 1).Interior.ColorIndex = 0
ws.Cells(i + 1, j).Interior.ColorIndex = 0
ws.Cells(i + 1, j + 1).Interior.ColorIndex = 0
ws.Cells(i + 2, j).Interior.ColorIndex = 0
End If
Exit For
End If
Next j
Exit For
End If
Next i

//clears the form
Me.sName.Value = ""
Me.hrsIn.Value = ""
Me.hrsOut.Value = ""
Me.abCode.Value = ""
Me.atComments.Value = ""

//focuses on the first field.
Me.sName.SetFocus

End Sub

 

[Joe4]

Yes, I'm deleting all the rows after the last row with valid data on it. After hitting CTRL-END, Excel takes me to the very last row.

But are you saving after you delete all those rows?
The CTRL-END setting won't change until AFTER you save the file again.

And be sure that you are actually deleting the rows, and not just clearing their contents (there is a difference).

 

[richh]

Ah I didn't know that part... No, I wasn't saving after deleting the rows. Saving would take about 30-60 seconds to complete, so I had been avoiding doing the function whenever possible.

I resolved the matter by just copying the data into new sheets, deleting the old ones, and renaming the new ones. Doing so chopped the workbook down from 70 MB to just over 500 KB.