Getting the actual usedrange

by

What sort of button, neither a Forms Control nor an ActiveX control should affect the used range.

It is a known problem that excel does not keep track of the used range very well. Any reference to the used range via VBA will reset the value to the current used range. So try running this sub procedure:

Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub

Failing that you may well have some formatting hanging round. Try clearing/deleting all the cells after your last row.

Regarding the above also see:

Excel Developer Tip

Another method to find the last used cell:

    Dim rLastCell As Range

    Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Change the search direction to find the first used cell.

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. Wait for shell command to complete [duplicate]
  3. Unique Random Numbers using VBA
  4. Populate unique values into a VBA array from Excel
  5. Save each sheet in a workbook to separate CSV files
  6. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  7. ShowAllData method of Worksheet class failed
  8. Detect if range is empty
  9. Checking if a worksheet-based checkbox is checked
  10. Assign keyboard shortcut to run procedure
  11. Uncheck all checkboxes across entire workbook via CommandButton
  12. return csv file as recordset
  13. VBA workbooks.Close without being prompted to if the user wants to save?
  14. Get text from clipboard using GetText – avoid error on empty clipboard
  15. Copy a selected range to another worksheet
  16. Benchmarking VBA Code
  17. Excel macro – paste only non empty cells from one sheet to another
  18. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  19. Pointers needed for speeding up nested loop macro in VBA
  20. Display milliseconds in Excel
  21. ‘1004’: “The sort reference is not valid.”
  22. Application.Quit command not closing the entire Excel Application
  23. Create hyperlink to another sheet
  24. Trying to open the workbook in separate instance
  25. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  26. Application.WorksheetFunction.Match method
  27. Delete all data rows from an Excel table (apart from the first)
  28. Excel Hide/Show all tabs on Ribbon except custom tab
  29. Macros Not Showing Up in Macro Table
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment