The ProblemFor 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"
The VBA non-fixes
The VBA fix
- 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 benefitsAs 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.
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
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)))