Stop adding rows!

  • Thread starter Thread starter Gnarlodious
  • Start date Start date
Gnarlie

You cannot delete unused rows columns but, if you want to not show the
rows/columns, then you could select them and go to Format>Row (Or
column)>Hide.

If you want to restrict movement then...

You can't restrict it in the standard user interface, but you can, either
through code or through the VBE.

In Code, in a standard module enter (this restricts the movement to row 70
and column Z)

Sub FreezeScrollAt70()
Worksheets("Sheet1").ScrollArea = "$A$1:$Z$70"
End Sub

Or in the VBE (Right click on a worksheet tab and select view code... Then
in the pane that appears look for the properties explorer, normally bottom
right. In here you will see a ScrollArea property. Enter the address you
want to restrict users to in absolute references. E.G $A$1:$Z$70.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Entity Nick Hodge spoke thus:
Gnarlie

You cannot delete unused rows columns but, if you want to not show the
rows/columns, then you could select them and go to Format>Row (Or
column)>Hide.

If you want to restrict movement then...

You can't restrict it in the standard user interface, but you can, either
through code or through the VBE.

In Code, in a standard module enter (this restricts the movement to row 70
and column Z)

Sub FreezeScrollAt70()
Worksheets("Sheet1").ScrollArea = "$A$1:$Z$70"
End Sub

Or in the VBE (Right click on a worksheet tab and select view code... Then
in the pane that appears look for the properties explorer, normally bottom
right. In here you will see a ScrollArea property. Enter the address you
want to restrict users to in absolute references. E.G $A$1:$Z$70.
Click to expand...

Thanks, Nick. However when I try to launch the VB editor all I get is an
"Out of memory" window. This despite having 1.28 GB RAM in this thing.

Oh well, I'll just suffer with infinite vertical scrolling.
 
Oh well, I'll just suffer with infinite vertical scrolling.

--
65,536 rows is not infinite. <g>

But why not just hide the extra rows as suggested?

Bill
 
Unfortunately hiding all those rows results in a 1.5 MB file while leaving
them unhid results in a 36 K file, so scratch that.

I wish there were a way to make a sheet of set dimensions.

-- Gnarlie


Entity Gnarlodious spoke thus:
 
Gnarlie

Hiding rows and/or columns should make no difference in workbook size.

You've got something else going on there.

Try this.....

Select all rows from 71:65536 using the SHIFT + END + Downarrow.

Edit>Delete to clear out everything.

Now Format>Hide those rows.

Save the workbook.

What is the size under File>Properties>General?

Gord Dibben Excel MVP
 
You posting is too ambiguous as to what you actually have,
and what you actually need and want.

Formatting cells for an entire column would actually save
space, and does not affect the used range. But placing
formulas, or spaces or anything into a column will increase
your used range and make your file larger. You should not
be creating formulas past where you need them.

If you use Ctrl+End you will see where your last cell is
as far as Excel is concerned. If it is beyond your data
then you should delete such rows and columns.
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

Excel may have 65536 rows X 256 columns but you can't
fill them all up with formulas and data and expect to work with
it with it. It would take more time than you could tolerate to
work with that.
 
You're right, I must have had nul data somewhere in those rows. By first
deleting the rows the 2 files are now exactly the same size.

Thanks.

-- Gnarlie
http://Gnarlodious.com/


Entity Gord Dibben spoke thus:
 
Back
Top