0

So, I've read around quite a bit and have come across the ActiveWorksheet.UsedRange function, as well as clearing all contents and deleting rows.

My worksheet has two sections to it; A1:AJ2900 and AL1:BX2900 - The second section is used to paste raw data, and the first section uses formulas to cross-reference the raw data across multiple sheets.

When I hit CTRL + End, I am taken to BX1048576.

I have tried the following:

  1. Select rows 2902 - 1048576 > Clear All

  2. Select Rows 2902 - 1048576 > Right Click row header > Delete -- When I do this, the formats of A1:AJ1 are copied and pasted to A2902:AJ1048576, when they were previously clear of formats.

  3. ActiveSheet.UsedRange

  4. Edit: Per this: https://support.microsoft.com/en-us/kb/244435, I have added the Excess Format Cleaner add-in.

After each of these, I saved the document and re-opened. No change.

I want to say that this happened before and there was a setting in Excel Options that did something? I might be thinking back to OpenOffice though, when I still used that.

MrMusAddict
  • 427
  • 1
  • 6
  • 16
  • Try saving with CTRL+S then see if the `CTRL+END` still goes beyond. Also, see [this answer](http://stackoverflow.com/a/7423252/4650297) for an alternative to `.UsedRange` – BruceWayne Sep 21 '16 at 17:24
  • Both of these unfortunately have no effect. – MrMusAddict Sep 21 '16 at 18:40
  • There are alternatives to using `.UsedRange` - are you interested in that, or are you trying to get to the bottom of why the CTRL+END is acting funky? (Both are fine, I'm just trying to determine what kind of info. I should look around for) – BruceWayne Sep 21 '16 at 18:48
  • Ultimately, I'm just trying to make it so that the scroll bar only scrolls between row 1 and 2900. Currently, it's scrolling from row 1 to 1048576. – MrMusAddict Sep 21 '16 at 18:50
  • How do you know between row 1 and 2900? If you can get the "last column" into a variable, you can adjust the range that way, no? (See the `Set rLastCell = ...` part of the answer I linked to above. Does that not give the correct cell? If not, does it return the *same* cell that CTRL+END goes to?) – BruceWayne Sep 21 '16 at 19:00

0 Answers0