Resetting worksheet UsedRange

The Problem

For years I've been frustrated by worksheet scroll controls for columns and rows going well beyond where there is data on the sheet.  This happens, for example, if someone pastes many rows of data then uses the filters to identify rows to delete, then deletes the contents (because that's much faster than deleting the rows) and re-sorting to bring all of the rows to the top.

From my Internet searches this is not an uncommon problem for Excel VBA users and programmers.

The User Interface "fix"

Supposedly, if one were to go to the row (or column) after the remaining data, select from there to the end of the used range (IOW, as far as the data used to be), the delete the rows/columns, this would fix the issue.   

I can only say I've had infrequent success with this method.

The VBA non-fixes

As usual, my method is to look at the object model first to see what I can deduce and infer from it.  The Worksheet object has a "UsedRange" property. 

When I see a property I want to update it directly with VBA. Unfortunately, UsedRange and it's properties are read-only, i.e. not something that can be updated directly from VBA.

When a property is read-only there is sometimes a method for updating it.  Not so in this case, from what I can see.

The VBA fix

After a bit of searching I found the answer was already developed and provided at xsformatcleaner.codeplex.com.  I won't repeat the code here but would like to add a few observations:

• The VBA is packaged in a xlam (Add-in) file, allowing users to have it as a utility available at all times, a good thing.
• When it is run, it addresses all worksheets of the active workbook.  I suggest revision to have it run on only the current worksheet...or put the  "For each worksheet" loop in one module the code inside the loop to use with the active worksheet
• Although I've not done it, the code is ready to be linked to a UI button or pop-up menu entry.

Additional benefits

As mentioned, this is now part of my arsenal of often called modules.  Many of the worksheets in the periodic reports are used and re-used, with each cycle, meaning the UsedRange should be re-set with each reporting cycle.  

Quite often, worksheets start with a large number of rows or columns that are then deleted by the report preparation process.  Resetting the UsedRange at the end of these procedures will make the output more usable without the user, or provider, taking the semi-manual step of running the code from the User Interface.

When worksheet are mainly rectangular data tables, cleaning up the UsedRange of the worksheet makes the UsedRange immediately available for searches, sorts, and other reporting activities.  Here is an example of the greater efficiency in programming

Sub X

   LeftMostColumn = LastWSColumn(ActiveWorksheet.Rows(1)) ' a function call

   BottomRow = LastWSRow(ActiveWorksheet.Columns(1)) ' Function call

   Set rng = ActiveWorksheet.Range(Cells(1,1),Cells(BottomRow,LeftMostColumn) 

   For each rngRow in rng.Rows

      For Each rowCell in rngRow.Columns

          <Do stuff>

      Next rowCell

   Next rngRow

Sub End 'SubX

That needs 5 variables and 2 functions, each with a 3-level parameter.  It also requires knowledge of which row and which column represent the longest and widest point of the worksheet, or additional functions/assignments to identify them.

The number of lines of code and variables could be reduced at the cost of  making the Set rng line more difficult to read and well-served by a continuation to the next line or two
   Set rng = ActiveWorksheet.Range(Cells(1,1),Cells( LastWSRow(ActiveWorksheet.Columns(1)),LastWSColumn(ActiveWorksheet.Rows(1))) 

Sub X_Prime

   CleanupWorksheet(ActiveWorksheet) '  Call the cleanup routine

   For each urRow in ActiveSheet.UsedRnge.Rows

      For each urRowCell in urRow.Columns
         <Do stuff>
      Next urRowCell

   Next urRow

Sub End 

This has the additional Sub (which has one, simple parameter), no functions, 2 Range variables instead of 3, fewer lines of code.

Last remark

Don't get me wrong.  The code on the top was miles ahead of where I first started with dealing with tables (forget ye not, I am self-taught) and I am still quite proud of the matched-pair functions,  LastWSColumn and LastWSRow