Hi All,
From my understanding, Excel's autosize feature for row heights has some
limitations as it only works on rows with 14 or less lines of text in a
cell.
I have two questions:-
1) Can anyone confirm the specifics of these limitations
2) Does anyone have any good ideas on how to workaround this (I am aware
that you can manually resize each row but as my sheet has over 700 entries
this would not be useable).
Any help or pointers would be gladly received.
Andi
Andi
Row height autosize is not limited to <14 rows of text. A row height is
limited to 409 points(72 points to the inch).
What you are most likely running into is the limits on viewable text in a
cell.
From Help on Specifications you will find that 32767 characters can be entered
in a cell.
However, only 1024 of these will be seen or can be printed.
To get around this 1024 limit follow RD's advice and sprinkle some AT +
ENTER's in every 1000 chars or so.
Postings have indicated that you can increase the 1024 up to several thousand.
Another work-around is to use a Text Box to store the text.
Gord Dibben Excel MVP
On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
<andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>Hi All,
>
>From my understanding, Excel's autosize feature for row heights has some
>limitations as it only works on rows with 14 or less lines of text in a
>cell.
>
>I have two questions:-
>
>1) Can anyone confirm the specifics of these limitations
>2) Does anyone have any good ideas on how to workaround this (I am aware
>that you can manually resize each row but as my sheet has over 700 entries
>this would not be useable).
>
>Any help or pointers would be gladly received.
>
>Andi
>
Gord,
It's not the point limit but I think it may be something to do with the
visible character per cell limit.
I have just done a character count on the visible text (visible text when
trying to use row autosize - clicking on line between rows) and it is
1,108chars including spaces, 924 without, which is fairly close to 1024.
The problem with your solution is that I have already got about 10 of
AT+ENTER's in this text already.
The cell's height won't autosize to show the entire text, but you can
manually size the row heights - this is time consuming though.
Regards
Andy
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:0vbv711tr9q7b9jo6qokmuul61e00f84uk@4ax.com...
> Andi
>
> Row height autosize is not limited to <14 rows of text. A row height is
> limited to 409 points(72 points to the inch).
>
> What you are most likely running into is the limits on viewable text in a
> cell.
>
> From Help on Specifications you will find that 32767 characters can be
entered
> in a cell.
>
> However, only 1024 of these will be seen or can be printed.
>
> To get around this 1024 limit follow RD's advice and sprinkle some AT +
> ENTER's in every 1000 chars or so.
>
> Postings have indicated that you can increase the 1024 up to several
thousand.
>
> Another work-around is to use a Text Box to store the text.
>
>
> Gord Dibben Excel MVP
>
> On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>
> >Hi All,
> >
> >From my understanding, Excel's autosize feature for row heights has some
> >limitations as it only works on rows with 14 or less lines of text in a
> >cell.
> >
> >I have two questions:-
> >
> >1) Can anyone confirm the specifics of these limitations
> >2) Does anyone have any good ideas on how to workaround this (I am aware
> >that you can manually resize each row but as my sheet has over 700
entries
> >this would not be useable).
> >
> >Any help or pointers would be gladly received.
> >
> >Andi
> >
>
Andi
Should have been ALT + ENTER, but I think you noticed that.
Are any cells merged?
Excel has a problem autofitting rows with merged cells.
You can resize manually or via Macro. Jim Rech has written
code for this.
http://groups.google.com/groups?thre...%40tkmsftngp05
Note also a recent adaptation of this code by Greg Wilson.
http://makeashorterlink.com/?P37D24B15
The best cure is DON"T USE MERGED CELLS. They cause no end of problems with
copying, pasting, sorting, filtering.
Gord
On Tue, 10 May 2005 11:03:45 +0100, "Andibevan"
<andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>Gord,
>
>It's not the point limit but I think it may be something to do with the
>visible character per cell limit.
>
>I have just done a character count on the visible text (visible text when
>trying to use row autosize - clicking on line between rows) and it is
>1,108chars including spaces, 924 without, which is fairly close to 1024.
>The problem with your solution is that I have already got about 10 of
>AT+ENTER's in this text already.
>
>The cell's height won't autosize to show the entire text, but you can
>manually size the row heights - this is time consuming though.
>
>Regards
>
>Andy
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:0vbv711tr9q7b9jo6qokmuul61e00f84uk@4ax.com...
>> Andi
>>
>> Row height autosize is not limited to <14 rows of text. A row height is
>> limited to 409 points(72 points to the inch).
>>
>> What you are most likely running into is the limits on viewable text in a
>> cell.
>>
>> From Help on Specifications you will find that 32767 characters can be
>entered
>> in a cell.
>>
>> However, only 1024 of these will be seen or can be printed.
>>
>> To get around this 1024 limit follow RD's advice and sprinkle some AT +
>> ENTER's in every 1000 chars or so.
>>
>> Postings have indicated that you can increase the 1024 up to several
>thousand.
>>
>> Another work-around is to use a Text Box to store the text.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
>> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>>
>> >Hi All,
>> >
>> >From my understanding, Excel's autosize feature for row heights has some
>> >limitations as it only works on rows with 14 or less lines of text in a
>> >cell.
>> >
>> >I have two questions:-
>> >
>> >1) Can anyone confirm the specifics of these limitations
>> >2) Does anyone have any good ideas on how to workaround this (I am aware
>> >that you can manually resize each row but as my sheet has over 700
>entries
>> >this would not be useable).
>> >
>> >Any help or pointers would be gladly received.
>> >
>> >Andi
>> >
>>
>
Gord,
No merged cells!! Having spent a bit of time messing around with different
combinations of text, characters and "Alt+Enter's" (Propper name hard
carriage return??) I believe that this is probably related to the cell
limitation you mentioned earlier. Are you certain about "To get around this
1024 limit follow RD's advice and sprinkle some AT +ENTER's in every 1000
chars or so." as I don't believe this works?
Kind Regards
Andi
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:fhi1819qci51h8o6oj99meu483l2ngle2s@4ax.com...
> Andi
>
> Should have been ALT + ENTER, but I think you noticed that.
>
> Are any cells merged?
>
> Excel has a problem autofitting rows with merged cells.
>
> You can resize manually or via Macro. Jim Rech has written
> code for this.
>
> http://groups.google.com/groups?thre...%40tkmsftngp05
>
> Note also a recent adaptation of this code by Greg Wilson.
>
> http://makeashorterlink.com/?P37D24B15
>
> The best cure is DON"T USE MERGED CELLS. They cause no end of problems
with
> copying, pasting, sorting, filtering.
>
>
> Gord
>
> On Tue, 10 May 2005 11:03:45 +0100, "Andibevan"
> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>
> >Gord,
> >
> >It's not the point limit but I think it may be something to do with the
> >visible character per cell limit.
> >
> >I have just done a character count on the visible text (visible text when
> >trying to use row autosize - clicking on line between rows) and it is
> >1,108chars including spaces, 924 without, which is fairly close to 1024.
> >The problem with your solution is that I have already got about 10 of
> >AT+ENTER's in this text already.
> >
> >The cell's height won't autosize to show the entire text, but you can
> >manually size the row heights - this is time consuming though.
> >
> >Regards
> >
> >Andy
> >
> >
> >"Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >news:0vbv711tr9q7b9jo6qokmuul61e00f84uk@4ax.com...
> >> Andi
> >>
> >> Row height autosize is not limited to <14 rows of text. A row height
is
> >> limited to 409 points(72 points to the inch).
> >>
> >> What you are most likely running into is the limits on viewable text in
a
> >> cell.
> >>
> >> From Help on Specifications you will find that 32767 characters can be
> >entered
> >> in a cell.
> >>
> >> However, only 1024 of these will be seen or can be printed.
> >>
> >> To get around this 1024 limit follow RD's advice and sprinkle some AT +
> >> ENTER's in every 1000 chars or so.
> >>
> >> Postings have indicated that you can increase the 1024 up to several
> >thousand.
> >>
> >> Another work-around is to use a Text Box to store the text.
> >>
> >>
> >> Gord Dibben Excel MVP
> >>
> >> On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
> >> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
> >>
> >> >Hi All,
> >> >
> >> >From my understanding, Excel's autosize feature for row heights has
some
> >> >limitations as it only works on rows with 14 or less lines of text in
a
> >> >cell.
> >> >
> >> >I have two questions:-
> >> >
> >> >1) Can anyone confirm the specifics of these limitations
> >> >2) Does anyone have any good ideas on how to workaround this (I am
aware
> >> >that you can manually resize each row but as my sheet has over 700
> >entries
> >> >this would not be useable).
> >> >
> >> >Any help or pointers would be gladly received.
> >> >
> >> >Andi
> >> >
> >>
> >
>
Andi
Been playing with this and find the following, which is no help to you.
Enter =REPT(REPT("1234567890",12)&CHAR(10),30)
Gives 30 lines of 120 characters in the cell = 3630 chars(including the
CHAR(10)'s
Wrap text is set.
Column width at 121
Format>Row>Autofit shows 17 lines = 2040 characters
Column width at 136
Format>Row>Autofit shows 9 lines = 1080 characters plus whitespace.
Manually fit row height and can see all 3630 characters.
Not much rhyme nor reason as far as I can see.
Gord
On Tue, 10 May 2005 17:43:27 +0100, "Andibevan"
<andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>Gord,
>
>No merged cells!! Having spent a bit of time messing around with different
>combinations of text, characters and "Alt+Enter's" (Propper name hard
>carriage return??) I believe that this is probably related to the cell
>limitation you mentioned earlier. Are you certain about "To get around this
>1024 limit follow RD's advice and sprinkle some AT +ENTER's in every 1000
>chars or so." as I don't believe this works?
>
>Kind Regards
>
>Andi
>
>
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:fhi1819qci51h8o6oj99meu483l2ngle2s@4ax.com...
>> Andi
>>
>> Should have been ALT + ENTER, but I think you noticed that.
>>
>> Are any cells merged?
>>
>> Excel has a problem autofitting rows with merged cells.
>>
>> You can resize manually or via Macro. Jim Rech has written
>> code for this.
>>
>> http://groups.google.com/groups?thre...%40tkmsftngp05
>>
>> Note also a recent adaptation of this code by Greg Wilson.
>>
>> http://makeashorterlink.com/?P37D24B15
>>
>> The best cure is DON"T USE MERGED CELLS. They cause no end of problems
>with
>> copying, pasting, sorting, filtering.
>>
>>
>> Gord
>>
>> On Tue, 10 May 2005 11:03:45 +0100, "Andibevan"
>> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>>
>> >Gord,
>> >
>> >It's not the point limit but I think it may be something to do with the
>> >visible character per cell limit.
>> >
>> >I have just done a character count on the visible text (visible text when
>> >trying to use row autosize - clicking on line between rows) and it is
>> >1,108chars including spaces, 924 without, which is fairly close to 1024.
>> >The problem with your solution is that I have already got about 10 of
>> >AT+ENTER's in this text already.
>> >
>> >The cell's height won't autosize to show the entire text, but you can
>> >manually size the row heights - this is time consuming though.
>> >
>> >Regards
>> >
>> >Andy
>> >
>> >
>> >"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> >news:0vbv711tr9q7b9jo6qokmuul61e00f84uk@4ax.com...
>> >> Andi
>> >>
>> >> Row height autosize is not limited to <14 rows of text. A row height
>is
>> >> limited to 409 points(72 points to the inch).
>> >>
>> >> What you are most likely running into is the limits on viewable text in
>a
>> >> cell.
>> >>
>> >> From Help on Specifications you will find that 32767 characters can be
>> >entered
>> >> in a cell.
>> >>
>> >> However, only 1024 of these will be seen or can be printed.
>> >>
>> >> To get around this 1024 limit follow RD's advice and sprinkle some AT +
>> >> ENTER's in every 1000 chars or so.
>> >>
>> >> Postings have indicated that you can increase the 1024 up to several
>> >thousand.
>> >>
>> >> Another work-around is to use a Text Box to store the text.
>> >>
>> >>
>> >> Gord Dibben Excel MVP
>> >>
>> >> On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
>> >> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>> >>
>> >> >Hi All,
>> >> >
>> >> >From my understanding, Excel's autosize feature for row heights has
>some
>> >> >limitations as it only works on rows with 14 or less lines of text in
>a
>> >> >cell.
>> >> >
>> >> >I have two questions:-
>> >> >
>> >> >1) Can anyone confirm the specifics of these limitations
>> >> >2) Does anyone have any good ideas on how to workaround this (I am
>aware
>> >> >that you can manually resize each row but as my sheet has over 700
>> >entries
>> >> >this would not be useable).
>> >> >
>> >> >Any help or pointers would be gladly received.
>> >> >
>> >> >Andi
>> >> >
>> >>
>> >
>>
>
Gord,
I think your right - it isn't much help :-) , but it does show that there
probably is a slightly more complex relationship between visible cells,
autofit row height and characters in a cell. I presume that excel documents
its capacity as 1040 because that number of characters will ALWAYS be
displayed.
Thanks for your help
Andy
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:ua1281t42re3apnav7s7r9l574gpnrfi3s@4ax.com...
Andi
Been playing with this and find the following, which is no help to you.
Enter =REPT(REPT("1234567890",12)&CHAR(10),30)
Gives 30 lines of 120 characters in the cell = 3630 chars(including the
CHAR(10)'s
Wrap text is set.
Column width at 121
Format>Row>Autofit shows 17 lines = 2040 characters
Column width at 136
Format>Row>Autofit shows 9 lines = 1080 characters plus whitespace.
Manually fit row height and can see all 3630 characters.
Not much rhyme nor reason as far as I can see.
Gord
On Tue, 10 May 2005 17:43:27 +0100, "Andibevan"
<andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>Gord,
>
>No merged cells!! Having spent a bit of time messing around with different
>combinations of text, characters and "Alt+Enter's" (Propper name hard
>carriage return??) I believe that this is probably related to the cell
>limitation you mentioned earlier. Are you certain about "To get around
this
>1024 limit follow RD's advice and sprinkle some AT +ENTER's in every 1000
>chars or so." as I don't believe this works?
>
>Kind Regards
>
>Andi
>
>
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:fhi1819qci51h8o6oj99meu483l2ngle2s@4ax.com...
>> Andi
>>
>> Should have been ALT + ENTER, but I think you noticed that.
>>
>> Are any cells merged?
>>
>> Excel has a problem autofitting rows with merged cells.
>>
>> You can resize manually or via Macro. Jim Rech has written
>> code for this.
>>
>> http://groups.google.com/groups?thre...%40tkmsftngp05
>>
>> Note also a recent adaptation of this code by Greg Wilson.
>>
>> http://makeashorterlink.com/?P37D24B15
>>
>> The best cure is DON"T USE MERGED CELLS. They cause no end of problems
>with
>> copying, pasting, sorting, filtering.
>>
>>
>> Gord
>>
>> On Tue, 10 May 2005 11:03:45 +0100, "Andibevan"
>> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>>
>> >Gord,
>> >
>> >It's not the point limit but I think it may be something to do with the
>> >visible character per cell limit.
>> >
>> >I have just done a character count on the visible text (visible text
when
>> >trying to use row autosize - clicking on line between rows) and it is
>> >1,108chars including spaces, 924 without, which is fairly close to 1024.
>> >The problem with your solution is that I have already got about 10 of
>> >AT+ENTER's in this text already.
>> >
>> >The cell's height won't autosize to show the entire text, but you can
>> >manually size the row heights - this is time consuming though.
>> >
>> >Regards
>> >
>> >Andy
>> >
>> >
>> >"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> >news:0vbv711tr9q7b9jo6qokmuul61e00f84uk@4ax.com...
>> >> Andi
>> >>
>> >> Row height autosize is not limited to <14 rows of text. A row height
>is
>> >> limited to 409 points(72 points to the inch).
>> >>
>> >> What you are most likely running into is the limits on viewable text
in
>a
>> >> cell.
>> >>
>> >> From Help on Specifications you will find that 32767 characters can be
>> >entered
>> >> in a cell.
>> >>
>> >> However, only 1024 of these will be seen or can be printed.
>> >>
>> >> To get around this 1024 limit follow RD's advice and sprinkle some AT
+
>> >> ENTER's in every 1000 chars or so.
>> >>
>> >> Postings have indicated that you can increase the 1024 up to several
>> >thousand.
>> >>
>> >> Another work-around is to use a Text Box to store the text.
>> >>
>> >>
>> >> Gord Dibben Excel MVP
>> >>
>> >> On Mon, 9 May 2005 15:55:43 +0100, "Andibevan"
>> >> <andibevan@remove-to-prevent-spam-hotmail.com> wrote:
>> >>
>> >> >Hi All,
>> >> >
>> >> >From my understanding, Excel's autosize feature for row heights has
>some
>> >> >limitations as it only works on rows with 14 or less lines of text in
>a
>> >> >cell.
>> >> >
>> >> >I have two questions:-
>> >> >
>> >> >1) Can anyone confirm the specifics of these limitations
>> >> >2) Does anyone have any good ideas on how to workaround this (I am
>aware
>> >> >that you can manually resize each row but as my sheet has over 700
>> >entries
>> >> >this would not be useable).
>> >> >
>> >> >Any help or pointers would be gladly received.
>> >> >
>> >> >Andi
>> >> >
>> >>
>> >
>>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks