Find next cell with more than 1024 characters

[Souriane]

Good morning all:

I have a table with a few cells with about 2000 characters each . Of
course, Excel will not show the text after 1024. I would like to do a
macro that would find the next cell with more than 1024 characters, in
order to format the cell with the macro found on this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=421

I could do it manually but it is a merged document and I remerge this
document at least once a week.

Any help?

Thank you!

Souriane

 

[joel]

change just the main routine

from
Sub DisplayLongText()
'Adds line feed characters as required on cells in selection that are
longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
AddLineFeeds cel, col
Next
col = 0 'Force line length dialog to display the next time sub runs
End Sub

to
Sub DisplayLongText()
'Adds line feed characters as required on cells in selection that are
longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
if len(cel) > 1024 then
AddLineFeeds cel, col
end if
Next
col = 0 'Force line length dialog to display the next time sub runs
End Sub

 

[Souriane]

Thanks! Works great! Except for one thing :

If my sheet has 2 rows in a row that has a cell with more than 1024,
then the first letter of the text in the second row will be alone on
the first line like this:

ROW 1:
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une
bière et des kiwis. Servez à ce monsieur une bière et des kiwis.
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une

ROW 2 :
S
ervez à ce monsieur une bière et des kiwis. Servez à ce monsieur une
bière et des kiwis. Servez à ce monsieur une bière et des kiwis.
Servez à ce monsieur une bière et des kiwis. Servez à ce monsieur une

Any idea?...

 

[joel]

Not sure what length yo are using in the code. The line you posted only had
204 characters. Try changing the 1024 to 1023 or 1022. The comments in the
website code sometimes references 1022 and other times 1024. I believe the
code has to put two character (return and linefeed) in each line which
accounts for two characters. Therefore tthe code will only work when the
line is 1022 and not 1024.