Reset Last Cell on Worksheet

[turbotoan]

I have a spreadsheet with 1412 rows of data. However, everytime I CTRL + END, it takes me to the last column & row: 64965.

I tried running this VBA code, but it still finds 64965 as the last row.
ActiveSheet.UsedRange ' Reset the used range
ActiveCell.SpecialCells(xlLastCell).Activate

I also tried highlighting rows 1413 to the end (64965) and Edit --> CLEAR --> All . In addition, I did the same for the columns going across.
For whatever reason, it still does not see row 1412 as the last row.

Any suggestions?

 

[DJFudd]

Itried this but couldn't get it to work....

However I had a thought and I tried this, I went into PageBreakMode on the View tab. For each sheet that had extra columns I deleted them.
When I went back to check the end of sheet was where it should be.
Give it a try you will save yourself a lot of grief.

 

[G1zmos]

I am not sure why Microsoft recommends clearing the cells as deleting them should take care of everything nicely. Given that, I am pretty sure this shorter piece of code will do the same thing as Microsoft's code...

Sub ResetUsedRange()
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  LastUsedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
  Rows(LastUsedRow + 1).Resize(Rows.Count - LastUsedRow).Delete
  Columns(LastUsedCol + 1).Resize(, Columns.Count - LastUsedCol).Delete
End Sub

However, if you feel more comfortable clearing and deleting the unused rows and columns, then this...

Sub ResetUsedRange()
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  LastUsedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
  With Rows(LastUsedRow + 1).Resize(Rows.Count - LastUsedRow)
    .Clear
    .Delete
  End With
  With Columns(LastUsedCol + 1).Resize(, Columns.Count - LastUsedCol)
    .Clear
    .Delete
  End With
End Sub

Rick
I used the following code and for some reason it is deleting the last column of my worksheet.
My worksheet contains columns A-M and rows 1-363. When I used the code it deleted Column M???
Gizmos

Sub ResetUsedRange()
Dim LastUsedRow As Long, LastUsedCol As Long
LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
LastUsedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
With Rows(LastUsedRow + 1).Resize(Rows.Count - LastUsedRow)
.Clear
.Delete
End With
With Columns(LastUsedCol + 1).Resize(, Columns.Count - LastUsedCol)
.Clear
.Delete
End With
End Sub

 

[G1zmos]

Sub ResetUsedRange() Dim LastUsedRow As Long, LastUsedCol As Long LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row LastUsedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column Rows(LastUsedRow + 1).Resize(Rows.Count - LastUsedRow).Delete Columns(LastUsedCol + 1).Resize(, Columns.Count - LastUsedCol).Delete End Sub

I have tried both formulas described above and on a spreadsheet that I import to the desktop version of excel, from office 365, and when I select CTRL+END it takes me to row1048289 Column R even though there is no data after R105 Column L.
Any idea why?

 

[MARK858]

Try

Sub xTest()
Dim x as long
x = Application.ActiveSheet.UsedRange.Rows.Count
End Sub

Then try CTRL+END

If not what does the formula

=UNICODE(R1048289)

return?