Prevent scrolling to bottom row excel 2010

[shirty89]

Hi there,

I'm currently creating a workbook whereby I have (regrettably) highlighted entire columns and formatted these columns to show borders. The reason I did this was because drawing them in manually (I thought) would take much longer with the way the worksheet is laid out.

Obviously highlighting the entire column has meant that borders were drawn all the way down to the bottom row of the workbook, and so the vertical scroll bar cannot effectively be 'dragged' up and down as even a tiny movement will cause it to scroll right past the worksheet.

I have tried running a macro to clear contents and formatting for all the unused rows, but the scroll bar remains to be a pain.

I want the scroll bar to only scroll down to a few cells below the available work space like it should do.

Any Ideas?

Thanks
Sam

 

[Datsmart]

ActiveSheet.ScrollArea = UsedRange

The above code will set the Scrollarea to what is on the active page.

 

[Smitty]

Welcome to the Board!

Try deleting the unused rows, then save the workbook. Just clearing contents doesn't get Excel to forget the unused range, you actually need to delete it. Once you do that the scroll bar should reset.

HTH,

 

[shirty89]

Thanks for your replies.

I have tried using the above macro code with "Scrollarea Sub ()" which didn't work. Am I doing it wrong? Please forgive as I am new to VBE.

Deleting the unused rows would be impractical as i'd have to delete rows 1306 to 1002510... unless there's a code for deleting rows?

Sam

 

[Smitty]

Deleting the unused rows would be impractical as i'd have to delete rows 1306 to 1002510... unless there's a code for deleting rows?

No code needed-->Goto row 1306, then Shift+Ctrl+Down arrow, which will select to the bottom of the rows, then Alt+E+D+R to delete them.

 

[shirty89]

I have selected all the rows and when I press ALT+E+D+R I get a "find and replace" pop up box??

However I think I know what would happen even if your suggestion did work... I would get a message saying "does not have enough resources to continue", and then the sheet would also not display correctly. I am running a high end system with 8GB RAM but this is a relatively large sheet. I have to say I find the limitations of excel quite annoying.

 

[Smitty]

That was a keyboard shortcut and shouldn't pop up the Find & Replace dialog, which is Ctrl+H. If you have that row range selected, you can also go to the Home tab-->Cells-->Delete-->Delete Rows.

I see no reason why you'd get any error messages whatsoever, as I can do the same thing on a 4 year old laptop.

What limitations are you referring to?

 

[shirty89]

Hi Smitty

I think I have a much more serious problem at hand.

Now, even when I select one row and try to delete it, I get the message saying it's about to make changes to a large number of cells and may take a while to complete... and when I click ok, get the other error message saying doesn't have enough resources to complete the task! And then the other error message saying doesn't have enough resources to display everything properly, resulting in either a black worksheet or the cells being all over the place. What the hell??

It would appear I was wrong about the limitations in excel (I was referring to how much you can do before it eats up all your memory), so it looks like I might have to sort this out first before I can continue.

Any further assistance will be appreciated.

Sam

 

[Smitty]

It sounds like you may have inavertently created some unintended dependencies on those extra rows.

I'd try copying just the data you actually need to a new workbook and see if that helps.

 

[shirty89]

Hi Smitty

Yeah it looks like that's my only option. I have just tried and luckily it's letting me copy large parts of the original over. However in the new workbook it's saying it can only paste the information as values and not the actual formulas.

Do you know a way round this?

Thanks
Sam