Expanding the sheet

  • Thread starter Thread starter Student
  • Start date Start date
S

Student

Excel's single sheet is a 65,536x256 table. Please, can the latter
number be expanded?

Thank you very much.

Student
 
No, you cannot increase the number of columns. It is fixed at
256.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip Pearson said:
No, you cannot increase the number of columns. It is fixed at
256.
Click to expand...

Please, I am right to assume also that:

- the number of rows cannot be increased beyond 65,635,

- the number of interations cannot be increased beyond 10,000?

Thank you very much for your time.

Student
 
Yep for the rows.

But in xl2002, I could increase the number of iterations to 32767.
 
Dave said:
Yep for the rows.
Click to expand...

I see. Thank you very much. Please, how do I do it? What is the maximum of rows?
But in xl2002, I could increase the number of iterations to 32767.
Click to expand...

Right. I finally managed to get to 32767.
What do you mean by x12002, please?

Thank you for your time.

Student
 
You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)

xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.
 
I didn't HAVE other versions to test....

Dave said:
You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)

xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.
Click to expand...
 
Dave said:
You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)
Click to expand...

Yes. I see. You are absolutely right. My mistake, I am sorry.
xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.
Click to expand...

I see. Thank you.


Thank you very much for writing.

Student
P.S. Perhaps, if I am already not a nuisance to this newsgroup (I
mean, Excel is so great I just can't believe my eyes sometimes - to
say only that it's functionality is Turing complete, I am building a
complete 8088 circuitry in it(!). Please, what is the greatest number
of sheets in a single file? Is it 256?
 
Not a nuisance at all. (Sorry to confuse you before <g>.)

But the number of sheets in a workbook is limited by available memory. You can
add them until you can't add any more.

From a usability standpoint, I wouldn't put too many in one workbook, though.
It becomes hard to navigate and if you have lots of data, it becomes bulkier and
slower to do things.
 
Dave said:
Not a nuisance at all. (Sorry to confuse you before <g>.)
Click to expand...

You are very very kind.
But the number of sheets in a workbook is limited by available memory.
You can add them until you can't add any more.
Click to expand...

That's a relief. Thank you very much.
From a usability standpoint, I wouldn't put too many in one workbook, though.
It becomes hard to navigate and if you have lots of data, it becomes bulkier
and slower to do things.
Click to expand...

I see. Thank you.

OK. Perhaps, if I am not just pushing my luck with your kindness and
helpfulness, please, would you care to tell me how to create a button
which, when pushed, would invert the contents of a particular cell
associated with it(i.e. it would check the contents of a cell for a 0
or a 1, and rewrite it respectively with a 1 and a 0? I am afraid I
don't have the slightest idea of how to even start.

Thank you very much for your time.

Student
 
One way:

Public Sub InvertCell()
With ActiveCell 'Or, e.g.: With Range("A1")
If IsNumeric(.Value) Then _
If .Value = 0 Or .Value = 1 Then _
.Value = 1 - .Value
End With
End Sub

Put this in a regular code module and attach it to a button. If
you're not familiar with macros, you may want to look at David
McRitchied's site:

Getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
J.E. gave you a nice function that you could use, but you could also use a
helper column next to it and just put a formula like:

=1-A1

And drag down. 1's will be 0's and 0's will become 1's.
(but bad things will happen to values that aren't 1 or 0.)

Depending on what you're doing, this might be a way to keep the original value,
so you don't lose track of what was there.

(If you really want to just click a button and have it do the work, then J.E.'s
method is the way to go.)

And just a little additional info for J.E.'s method.

View|Toolbars|and select Forms
click on the button icon and draw it on the sheet.
When you let go, you can assign J.E.'s macro to it.
(If you don't do it now, you can right click on the button and assign it later.)

And I'd put it in Row 1 (and make row 1 a little taller).

Then click on B1 and Window|Freeze Panes.

Then row 1 (and the button is always visible).
 
J.E. McGimpsey said:
One way:

Public Sub InvertCell()
With ActiveCell 'Or, e.g.: With Range("A1")
If IsNumeric(.Value) Then _
If .Value = 0 Or .Value = 1 Then _
.Value = 1 - .Value
End With
End Sub
Click to expand...

Thank you for your interest. I will do my best to implement this code
(I am afraid I do not have much experience in this regard).
Put this in a regular code module and attach it to a button. If
you're not familiar with macros, you may want to look at David
McRitchied's site:

Getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...

So thank you very much for the link. This is really very kind of you.

Best regards,
Student
 
Dave said:
J.E. gave you a nice function that you could use, but you could also use a
helper column next to it and just put a formula like:

=1-A1

And drag down. 1's will be 0's and 0's will become 1's.
(but bad things will happen to values that aren't 1 or 0.)
Right.

Depending on what you're doing, this might be a way to keep the original value,
so you don't lose track of what was there.

(If you really want to just click a button and have it do the work, then J.E.'s
method is the way to go.)
Click to expand...

Yes. Right.
And just a little additional info for J.E.'s method.

View|Toolbars|and select Forms
click on the button icon and draw it on the sheet.
When you let go, you can assign J.E.'s macro to it.
(If you don't do it now, you can right click on the button and assign it later.)

And I'd put it in Row 1 (and make row 1 a little taller).

Then click on B1 and Window|Freeze Panes.

Then row 1 (and the button is always visible).
Click to expand...

Thank you very much indeed. I will try to do it this way.

Thank you very much for writing.

Student
 
Back
Top