Change the scrollbar

To reset the range you can do;
1) after deleting the maverick number, immediately
save the file
2) Via code
Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

Ivan

Thank you.

Trial and Error

Ivan's advice solved the problem as posted, but my actual situation was messier. I had data not just in one, but in a dozen cells down there, and some exotic formatting to boot. The sheet had been saved and opened and closed several times. I deleted the data. No result. Oops, forgot to delete the formatting. No result. Highlight rows 31 to 3000 and Clear All. No result. Highlight rows 31 to 3000 and Delete them. That worked. Next, in the spirit of tinkering, I tried to pin down ActiveSheet.UsedRange to something more specific, so I highlighted my sheet from A1 to M30 (where my data are) and named it RangeEverything. Then I ran ActiveSheet.RangeEverything. It deleted everything, and Ctrl-Z didn't help. (Quitting without saving saved my bacon.) I still wonder if there is an explicit way to set that scrollbar.

Re: Trial and Error

CP
Check whether this does it :

Sub Reset_Last_Cell()
Dim LastRow As Long, lastCol As Long
Dim LastCell As Range
With ActiveSheet
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
lastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
Set LastCell = Cells(LastRow, lastCol)
With LastCell
Range(.Offset(0, 1), Cells(.Row, 256)).EntireColumn.Delete
Range(.Offset(1, 0), Cells(65536, .Column)).EntireRow.Delete
End With
ActiveWorkbook.Save
End Sub

Celia

Scorched earth

It took me a moment to interpret your prescription. (It never fails. The very minute I start to feel smug about my VB scope, up pops an Advanced Tutorial.) As I understand it, the first part of your macro nails down the outside edges of my used range, and the second part is the nuclear weapon, scorched earth solution. I like it. With that philosophy in mind, I tried a non-macro approach. Use Ctrl - End to find the outside corner (This turned out to be an interesting exercise. I tried this on several of my other projects, and half the time my used range was not what I thought.), then Delete all the rows and columns below and to the right. The busy work involves tracking down the garbage that may be lingering in the sticks, then determining if it really is temporary/mistake/useless/ditchable stuff, and not crucial data/formulas stored off to the side so as not to distract the user from my carefully designed, finely polished interface. I think some of the problems with my original round of tinkering came from not following Ivan's advice to the letter: I was inconsistent with my saving between operations. Saving turns out to be important.
My impression is that Excel recognizes the scrollable range by default. That is, wherever there are data, that region defines the UsedRange, and UsedRange is the only pointer Excel utilizes to set the scroll. In other words, the user cannot define some other range for Excel to use for the purpose of scrolling. The scroll range can never be less than the UsedRange, and the only way to make it larger is to resize the UsedRange by sticking a number/format out there where you require it.

Re: Scorched earth

Excel allocates memory based on the last cell used
(and that includes the application of a format).
All one needs to do to free-up this allocation is:

1. Perform an Edit | Go To... | Special... Last Cell

2. Delete all rows from the selected (last cell)
up to the bottom-most row that you want to keep.

3. Delete all columns from the selected (last cell)
up to the right-most column that you want to keep.

4. Save the the worksheet. It took me a moment to interpret your prescription. (It never fails. The very minute I start to feel smug about my VB scope, up pops an Advanced Tutorial.) As I understand it, the first part of your macro nails down the outside edges of my used range, and the second part is the nuclear weapon, scorched earth solution. I like it. With that philosophy in mind, I tried a non-macro approach. Use Ctrl - End to find the outside corner (This turned out to be an interesting exercise. I tried this on several of my other projects, and half the time my used range was not what I thought.), then Delete all the rows and columns below and to the right. The busy work involves tracking down the garbage that may be lingering in the sticks, then determining if it really is temporary/mistake/useless/ditchable stuff, and not crucial data/formulas stored off to the side so as not to distract the user from my carefully designed, finely polished interface. I think some of the problems with my original round of tinkering came from not following Ivan's advice to the letter: I was inconsistent with my saving between operations. Saving turns out to be important.

NOW I find message # 6517

And # 6644 (You were very patient, Ivan.)

Re: And # 8063

Re: And # 9477