none
Excel 2016 reset last cell RRS feed

  • Question

  • Hi,

    There are enough samples on the internet on how to reset the last cell but none seems to work for Excel 2016.

    I have a worksheet with 4 columns (A1:En) and a macro is querying a database and add data in the columns E to AO. From time to time I have to do it over and delete the data in E:AO. 

    With A1 selected, CTRL+SHIFT+END still return the range A:AO. Even after deleting all cells, CTRL+SHIFT+END return A:AO.

    As the macro can also add rows, it happens that the last cell is AO11000. Is there another way in Excel 2016 to reset this?

     

    Wednesday, January 30, 2019 8:31 AM

Answers

  • A long time ago, I created the following macro. See if it works for you.
    As it is, it resets the used range for all worksheets in the active workbook; it wouldn't be difficult to modify it to operate on the active sheet only.


    Sub Cleanup()
      Dim r As Long, tr As Long, c As Long, tc As Long
      Dim ws As Worksheet, ur As Range, ar As Range, sh As Shape
      Dim fc As Boolean, fd As Boolean, fs As Boolean
      Dim s As String
      If ActiveWorkbook Is Nothing Then Exit Sub
      On Error Resume Next
      Application.ScreenUpdating = False
      For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Checking " & ws.Name & ", Please Wait..."
        fc = ws.ProtectContents
        fd = ws.ProtectDrawingObjects
        fd = ws.ProtectScenarios
        ws.Unprotect
        r = 0
        c = 0
        Set ur = Union(ws.UsedRange.SpecialCells(xlCellTypeConstants), _
          ws.UsedRange.SpecialCells(xlCellTypeFormulas))
        If Err.Number = 1004 Then
          Err.Clear
          Set ur = ws.UsedRange.SpecialCells(xlCellTypeConstants)
        End If
        If Err.Number = 1004 Then
          Err.Clear
          Set ur = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        End If
        If Err.Number = 0 Then
          For Each ar In ur.Areas
            tr = ar.Range("A1").Row + ar.Rows.Count - 1
            tc = ar.Range("A1").Column + ar.Columns.Count - 1
            If tc > c Then c = tc
            If tr > r Then r = tr
          Next ar
          For Each sh In ws.Shapes
            tr = sh.BottomRightCell.Row
            tc = sh.BottomRightCell.Column
            If tc > c Then c = tc
            If tr > r Then r = tr
          Next sh
          ws.Rows(r + 1 & ":" & ws.Rows.Count).Delete
          ws.Range(ws.Cells(1, c + 1), ws.Cells(1, ws.Columns.Count)).EntireColumn.Delete
        Else
          Err.Clear
        End If
        ws.Protect DrawingObjects:=fd, Contents:=fc, Scenarios:=fs
        s = ws.UsedRange.Address
      Next ws
      Set sh = Nothing
      Set ar = Nothing
      Set ur = Nothing
      Set ws = Nothing
      Application.StatusBar = False
      Application.ScreenUpdating = True
      MsgBox "Superfluous rows and columns have been removed.", vbInformation
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Thursday, January 31, 2019 4:04 PM
    Wednesday, January 30, 2019 7:42 PM

All replies

  • Try referring to ActiveSheet.UsedRange in the Visual Basic Editor.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 30, 2019 10:01 AM
  • Hi Hans,

    I tried this already, no avail.

    Wednesday, January 30, 2019 10:13 AM
  • Did you try to Save the active workbook after delete?

    Guy Zommer

    Wednesday, January 30, 2019 11:50 AM
  • Hi JP,

    Ctrl+Shift+End is used to recognize the UsedRange on the Worksheet.

    Once you delete the data in UsedRange, it only deletes the data but doesn't change the UsedRange which is still the same and that tells you that what was the range which was used on the worksheet.

    But once you save the Workbook after deleting the content, the UsedRange will also be reset and if you try the same keystroke Ctrl+Shift+End, the selection will remain in A1.

    So after deleting the content on the Worksheet, you need to use ActiveWorkbook.Save to reset the UsedRange.

    Hope this helps.


    Subodh Tiwari (Neeraj) sktneer

    Wednesday, January 30, 2019 3:14 PM
  • Hi All,

    I've made some more tests and came to the following conclusions:

    1. The last cell had no formatting like interior color, only data. ActiveSheet.UsedRange resets the usedrange.
    2.  Last cell has had a format like interior color: ActiveSheet.UsedRange does not change this. Deleting the last cell and apply ActiveSheet.UsedRange resets it.

    Would someone be so kind to reproduce these steps and confirm?

    My Excel version is 1812 buils 11126.202666

    Wednesday, January 30, 2019 6:00 PM
  • If the last cell contains any kind of formatting, deleting the content only will not reset the UsedRange.

    How are you deleting the content from the Worksheet through VBA?

    If yes, use Clear instead of ClearContents. Clear will delete everything from the cells and will reset the UsedRange.

    If you are deleting the content manually, try a keystroke Alt+E+A+A instead of just Delete, this will delete everything from the cells.


    Subodh Tiwari (Neeraj) sktneer

    Wednesday, January 30, 2019 6:29 PM
  • A long time ago, I created the following macro. See if it works for you.
    As it is, it resets the used range for all worksheets in the active workbook; it wouldn't be difficult to modify it to operate on the active sheet only.


    Sub Cleanup()
      Dim r As Long, tr As Long, c As Long, tc As Long
      Dim ws As Worksheet, ur As Range, ar As Range, sh As Shape
      Dim fc As Boolean, fd As Boolean, fs As Boolean
      Dim s As String
      If ActiveWorkbook Is Nothing Then Exit Sub
      On Error Resume Next
      Application.ScreenUpdating = False
      For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Checking " & ws.Name & ", Please Wait..."
        fc = ws.ProtectContents
        fd = ws.ProtectDrawingObjects
        fd = ws.ProtectScenarios
        ws.Unprotect
        r = 0
        c = 0
        Set ur = Union(ws.UsedRange.SpecialCells(xlCellTypeConstants), _
          ws.UsedRange.SpecialCells(xlCellTypeFormulas))
        If Err.Number = 1004 Then
          Err.Clear
          Set ur = ws.UsedRange.SpecialCells(xlCellTypeConstants)
        End If
        If Err.Number = 1004 Then
          Err.Clear
          Set ur = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        End If
        If Err.Number = 0 Then
          For Each ar In ur.Areas
            tr = ar.Range("A1").Row + ar.Rows.Count - 1
            tc = ar.Range("A1").Column + ar.Columns.Count - 1
            If tc > c Then c = tc
            If tr > r Then r = tr
          Next ar
          For Each sh In ws.Shapes
            tr = sh.BottomRightCell.Row
            tc = sh.BottomRightCell.Column
            If tc > c Then c = tc
            If tr > r Then r = tr
          Next sh
          ws.Rows(r + 1 & ":" & ws.Rows.Count).Delete
          ws.Range(ws.Cells(1, c + 1), ws.Cells(1, ws.Columns.Count)).EntireColumn.Delete
        Else
          Err.Clear
        End If
        ws.Protect DrawingObjects:=fd, Contents:=fc, Scenarios:=fs
        s = ws.UsedRange.Address
      Next ws
      Set sh = Nothing
      Set ar = Nothing
      Set ur = Nothing
      Set ws = Nothing
      Application.StatusBar = False
      Application.ScreenUpdating = True
      MsgBox "Superfluous rows and columns have been removed.", vbInformation
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Thursday, January 31, 2019 4:04 PM
    Wednesday, January 30, 2019 7:42 PM
  • Hi Hans,

    My code is very similar to yours. I tested it on my work PC and it did not work. Today I did the same tests on my personal PC and do not have issues. It looks as I must look elsewhere.

    Regards,

    JP

    Thursday, January 31, 2019 4:04 PM