Getting rid of unused portions of worksheets

  • Thread starter Thread starter terrapinie
  • Start date Start date
T

terrapinie

I have a workbook comprised of several sheets. I have formulas that I
am entering into one or two cells in columns C or D, then copying the
formatting to the rest of the cells in the same row, up to AK or AL.
I want to know if it's possible to set up an 'end column' so when I
drag a cell, or want to highlight a series of cells, the sheet stops
when I get to the end of the active cells, instead of flying past it
out into hundreds of columns I'm not using. It's not a huge deal - it
would just make setting up these formulas a whole lot easier and
quicker if I can copy and fill without extending way past my active
cells then trying to come back to where I need to be.
Is there anyway to do this?

Thanks,
Laurie
 
Try setting the the scroll area

Sub setscroll()
ActiveSheet.ScrollArea = "$A1:AK" 'to set
'ActiveSheet.ScrollArea = "" 'to cancel
End Sub
 
Laurie,

In addition to Don's suggestion of limiting the scroll area (which must be
done each time the workbook is opened, so you might want to put the macro in
Workbook_Open), you might also want to hide the columns and rows beyond your
area. It makes a nice visual, as no columns appear beyond, only a gray
background. To do that, select the first column beyond your area (click in
the column header). Select all the columns to the right (Ctrl Shift
RightArrow). Right-click a column header and select Hide. Same for rows.
 
I don't think Earl, that would do what the OP is looking to accomplish.

If you click on a cell to copy, as the OP is looking to do, and you drag up
to the last visible column, even though it appears everything stops, the
copy is still extending into the hidden areas.
If you would hold the mouse at the border for 2 or 3 seconds, the copy could
extend 100 columns, even though you wouldn't see this until you unhid the
columns.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Laurie,

In addition to Don's suggestion of limiting the scroll area (which must be
done each time the workbook is opened, so you might want to put the macro in
Workbook_Open), you might also want to hide the columns and rows beyond your
area. It makes a nice visual, as no columns appear beyond, only a gray
background. To do that, select the first column beyond your area (click in
the column header). Select all the columns to the right (Ctrl Shift
RightArrow). Right-click a column header and select Hide. Same for rows.
 
RD,

Yes. But if the scroll area has been limited, per the earlier post, that'll
keep 'em out of that part. The hiding of rows and columns is just for
appearance. I generally do both. Looks fine - lasts a long time! :)
It's a good bit easier to hide the rows and columns first, also, as it's a
bit difficult with the scroll area set -- you can't get to them.
 
Thanks for the quick responses. I tried the macro and the hiding.
When I scroll over, the little yellow box pops up that tells you #
rows x # columns selected. This box still goes to 250 some columns
and 65,000 some rows. So that apparently didn't work.
However, hiding the columns and rows definitely did. It atleast makes
the page stop near the end of the active cells (I gave myself a couple
columns and rows to work with) so I can make the selection stop where
I need it to. It is definitely much better than hundreds of columns
or rows flying back and forth on the screen.
And hiding them is so obvious - I don't know why I didn't think of it
before!!! I guess sometimes the best solutions stare you in the face,
but you just can't see them!! ;-)

Thanks Again-
Laurie
 
Before reading this post using a column of dots at the end of the
working area coupled with the keyboard -End-+navigate keys to go to the
end of the row did the job. Learn something new everyday ;) thanks
 
Back
Top