sheet size

J

JE McGimpsey

Harvey Waxman said:
I know this was answered some time ago but I can't find it now.

How can I remove the many unused rows and/or columns from a worksheet?
Click to expand...


All XL sheets have 256 columns and 65536 rows - you can't remove them.

You can hide them, however. Assume your used range is A1:J50. Then, in
the Name Box (on the left of the Formula bar), enter "51:65536", then
choose Format/Row/Hide. Similarly, enter "K:IV" in the name box, and
choose Format/Column/Hide.

Note: This won't prevent your user from using the cursor keys or the
name box to access hidden cells. For that, you'll need VBA. Post back
with more info if you're interested.
 
H

Harvey Waxman

JE McGimpsey said:
You can hide them, however. Assume your used range is A1:J50. Then, in
the Name Box (on the left of the Formula bar), enter "51:65536", then
choose Format/Row/Hide. Similarly, enter "K:IV" in the name box, and
choose Format/Column/Hide.
Click to expand...

Thanks for the suggestion. This would do enough for me but it doesn't seem to
work as expected.

There are many rows beyond the 50th row. Not thousands but about a hundred.

Does the cursor need to be anyplace special? I assume the quotes aren't
required- didn't make any difference in any case.


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
J

JE McGimpsey

Harvey Waxman said:
it doesn't seem to work as expected
Click to expand...

How does it work?
There are many rows beyond the 50th row. Not thousands but about a hundred.
Click to expand...

No, there are thousands. If you place your cursor in the "bottom" row
and use the cursor keys or the scroll bar arrows, you can scroll down to
row 65536
Does the cursor need to be anyplace special? I assume the quotes aren't
required- didn't make any difference in any case.
Click to expand...

Cursor location doesn't matter, and quotes are not required.
 
H

Harvey Waxman

JE McGimpsey said:
How does it work?


No, there are thousands. If you place your cursor in the "bottom" row
and use the cursor keys or the scroll bar arrows, you can scroll down to
row 65536


Cursor location doesn't matter, and quotes are not required.
Click to expand...


I guess I was expecting too much and I think that your suggestion will be
adequate for my needs. I had assumed that if I asked for all rows beyond row X
to be hidden, then all rows beyond row X would be hidden.

The extra few dozen or so are not a real problem.

Thanks for the information

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
J

JE McGimpsey

Harvey Waxman said:
I guess I was expecting too much and I think that your suggestion will be
adequate for my needs. I had assumed that if I asked for all rows beyond row
X
to be hidden, then all rows beyond row X would be hidden.
Click to expand...

If you enter X+1:65536 in the name box, you'll get all rows after X.
 
H

Harvey Waxman

JE McGimpsey said:
If you enter X+1:65536 in the name box, you'll get all rows after X.
Click to expand...

The only thing that happens is that the row in which the cursor resides becomes
hidden. The total number of visible rows remains the same with the exception
of the single hidden row.

Clearly I must not be following your directions. I put 150:65536 in the name
box, then chose Format>Rows>Hide.

I then defined a name using the specified number of rows. Then when I enter
the name in the name box and choose hide, it does work as advertised. Did I
misunderstand your original directions to enter the rows directly in the name
box? This is a little extra work but it does what I need.


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
J

JE McGimpsey

Harvey Waxman said:
The only thing that happens is that the row in which the cursor resides
becomes
hidden. The total number of visible rows remains the same with the exception
of the single hidden row.

Clearly I must not be following your directions. I put 150:65536 in the name
box, then chose Format>Rows>Hide.
Click to expand...

I was unclear - when you enter the range of rows in the name box you
need to hit Return to select those rows. You should then see that more
than one row is selected.
 
H

Harvey Waxman

JE McGimpsey said:
I was unclear - when you enter the range of rows in the name box you
need to hit Return to select those rows. You should then see that more
than one row is selected.
Click to expand...

Unless I first define the rows (150:65536) with a name in the
Insert>Name>Define dialogue, this doesn't work for me. I get a "name not
valid" error when I hit Return. If I define the rows, as above, entering the
name I created in the name box works as you describe.

I'm using Excel for OSX not 2004 if that makes a difference.

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
J

JE McGimpsey

Harvey Waxman said:
Unless I first define the rows (150:65536) with a name in the
Insert>Name>Define dialogue, this doesn't work for me. I get a "name not
valid" error when I hit Return. If I define the rows, as above, entering the
name I created in the name box works as you describe.

I'm using Excel for OSX not 2004 if that makes a difference.
Click to expand...

It doesn't for me.
 
J

Jim Gordon MVP

Hi Harvey,

Try this...

View > Page Break Preview

Then adjust the zoom control to 100%

-Jim
--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info
 
H

Harvey Waxman

Jim Gordon MVP said:
Then adjust the zoom control to 100%
Click to expand...

That sort of works but the lines and number are distracting. Sorry to be such a
pest. I can live with it the way it is but I was hoping the suggestion about
the name entry might work better for me.

Thanks anyway. Hope you all had a great holiday.


--
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
H

Harvey Waxman

JE McGimpsey said:
You're using R1C1 notation, aren't you?

try entering R150:R65536
Click to expand...

I tired. As I said, the only thing that results is that the row the cursor is
in disappears.

I surely am puzzled and appreciate your efforts. I wonder if there isn't some
preference buried somewhere that I might trash to see if it makes a difference.

I tried logging in as a new user but to no avail.


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 
B

Bob Greenblatt

I tired. As I said, the only thing that results is that the row the cursor is
in disappears.

I surely am puzzled and appreciate your efforts. I wonder if there isn't some
preference buried somewhere that I might trash to see if it makes a
difference.

I tried logging in as a new user but to no avail.
Click to expand...
Harvey,

Press F5. In the reference, type x:65536 where x is the FIRST row that you
want to be hidden. Press Return and then choose Format-Row-Hide from the
menu. Likewise, do it again for the columns but typing Z:IV or what ever you
want for the first column to be hidden.

You do not need to define any names.
 
H

Harvey Waxman

Bob Greenblatt said:
Press F5. In the reference, type x:65536 where x is the FIRST row that you
want to be hidden. Press Return and then choose Format-Row-Hide from the
menu. Likewise, do it again for the columns but typing Z:IV or what ever you
want for the first column to be hidden.

You do not need to define any names.
Click to expand...

Perfect! Thanks

I truly appreciate the effort of all. Very giving people here.


--
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

restrict calculation 1
keyboard shortcuts 2
Formula needed 2
update 2
Where is my error? 7
sort question 2
Frequency question 5
Can I do this? 1

Top