Reset Last Cell on Worksheet

T

turbotoan

Board Regular
Joined
Feb 25, 2002
Messages
62
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
A suggestion!

Rich (BB code): 
Sub Reset_LastCell()
' http://support.microsoft.com/default.aspx?scid=kb;en-us;244435&Product=xlw2K
   ' Save the lastcell and start there.
   Set lastcell = Cells.SpecialCells(xlLastCell)
   ' Set the rowstep and column steps so that it can move toward
   ' cell A1.
   rowstep = -1
   colstep = -1
   ' Loop while it can still move.
   While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
      ' Test to see if the current column has any data in any
      ' cells.
      If Application _
            .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
            > 0 Then colstep = 0  'If data then stop the stepping
         ' Test to see if the current row has any data in any cells.
         ' If data exists, stop row stepping.
         If Application _
               .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
               > 0 Then rowstep = 0
            ' Move the lastcell pointer to a new location.
            Set lastcell = lastcell.Offset(rowstep, colstep)
            ' Update the status bar with the new "actual" last cell
            ' location.
            Application.StatusBar = "Lastcell: " & lastcell.Address
   Wend
   ' Clear and delete the "unused" columns.
   With Range(Cells(1, lastcell.Column + 1), "IV65536")
      Application.StatusBar = "Deleting column range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Clear and delete the "unused" rows.
   With Rows(lastcell.Row + 1 & ":65536")
      Application.StatusBar = "Deleting Row Range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Select cell A1.
   Range("a1").Select
   ' Reset the status bar to the Microsoft Excel default.
   Application.StatusBar = False
End Sub

HTH
 
Upvote 0
Thank you Iridium! That code is awesome! Works like a charm.
NOTE: I needed to save, close Excel & re-open to see the effect!
Neat!
 
Upvote 0
You are Amazing bud...great simple fix to a huge problem...

Iridium said:
A suggestion!

Rich (BB code): 
Sub Reset_LastCell()
' http://support.microsoft.com/default.aspx?scid=kb;en-us;244435&Product=xlw2K
   ' Save the lastcell and start there.
   Set lastcell = Cells.SpecialCells(xlLastCell)
   ' Set the rowstep and column steps so that it can move toward
   ' cell A1.
   rowstep = -1
   colstep = -1
   ' Loop while it can still move.
   While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
      ' Test to see if the current column has any data in any
      ' cells.
      If Application _
            .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
            > 0 Then colstep = 0  'If data then stop the stepping
         ' Test to see if the current row has any data in any cells.
         ' If data exists, stop row stepping.
         If Application _
               .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
               > 0 Then rowstep = 0
            ' Move the lastcell pointer to a new location.
            Set lastcell = lastcell.Offset(rowstep, colstep)
            ' Update the status bar with the new "actual" last cell
            ' location.
            Application.StatusBar = "Lastcell: " & lastcell.Address
   Wend
   ' Clear and delete the "unused" columns.
   With Range(Cells(1, lastcell.Column + 1), "IV65536")
      Application.StatusBar = "Deleting column range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Clear and delete the "unused" rows.
   With Rows(lastcell.Row + 1 & ":65536")
      Application.StatusBar = "Deleting Row Range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Select cell A1.
   Range("a1").Select
   ' Reset the status bar to the Microsoft Excel default.
   Application.StatusBar = False
End Sub

HTH
Click to expand...
 
Upvote 0
Iridium said:
A suggestion!

Rich (BB code): 
Sub Reset_LastCell()
' http://support.microsoft.com/default.aspx?scid=kb;en-us;244435&Product=xlw2K
'
'   [[code snipped for length]]
'
End Sub
Click to expand...
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...

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...

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
  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
 
Upvote 0
Hey guys,

I have found more than a few Cures for for this problem using
Code: 
[SIZE=2]      [FONT=Verdana][SIZE=3]ActiveSheet.Used[/SIZE][/FONT]

               'or [/SIZE][SIZE=2]

 [/SIZE][FONT=Verdana][SIZE=3]Dim sh As  Worksheet, x As Long
    For Each sh In  ActiveWorkbook.Worksheets
      x =  sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
    Next  sh[/SIZE][/FONT]
but even clearing and/or deleting whole rows and columns has not fixed the problem with one of my workbooks. It is pretty old and has been used for about 10 years. I think I will have to copy it to a new workbook but it would sure be nice to have a piece of code that would fix this issue.
 
Upvote 0
Rick Rothstein,

your first code works like a charm.
When I tried the method described on the official Microsoft cell, spreadsheet file size turned from 5mb to 40+mb (!).
After I ran your macro, file size went from 5mb to 222kb.

Thanks a lot

tabibito
 
Upvote 0
Rick Rothstein said:
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...

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...

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
  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
Click to expand...

Kudoz to Rick Rothstein! As usual a huge help for nasty problematic spreadsheet. With this plus help from the forum member who suggested the activesheet.used range, this cleared my sheet of issues, reset it, then my application was to select the active cell to the last populated cell. Works great! Thanks guys.
Code: 
Sub ResetUsedRange()

'----clear
  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

'---reset
ActiveSheet.UsedRange

'---select
Range(ActiveCell, ActiveCell.SpecialCells(xlCellTypeLastCell)).Select
End Sub
 
Upvote 0
gdesreu said:
Kudoz to Rick Rothstein! As usual a huge help for nasty problematic spreadsheet. With this plus help from the forum member who suggested the activesheet.used range, this cleared my sheet of issues, reset it, then my application was to select the active cell to the last populated cell. Works great! Thanks guys................
Click to expand...




Hi,
.. I had a similar problem. I could not get any answers to the Thread I posted, and eventually I half answered it myself in
( http://www.mrexcel.com/forum/excel-...%3D-y-%96-1-usedrange-rows-count-anomale.html )
might be worth a quick glance
Alan Elston.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
102
Peter_SSs
Peter_SSs
DPChristman
  • Question Question
File too big - Causes excel to not respond for several minutes.
Replies
2
Views
78
Joe4
Joe4
D
  • Question Question
CTRL + End in table not going to last cell in table
Replies
8
Views
237
Alex Blakenburg
A
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
341
Micron
M
J
  • Solved
Speeding up VBA
Replies
9
Views
174
jvoss
J

Forum statistics

Threads
1,223,981
Messages
6,175,767
Members
452,668
Latest member
mrider123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top