Circumventing silly 255 char limit when copying entire sheet

TryingMyBest

New Member
Joined
Dec 9, 2005
Messages
4
I am having mucho problems because of Excel's 255 character limit when copying an entire spreadsheet to a new workbook.

Does anyone have ideas/code to circumvent it?

In a nutshell, I copy a "data entry" page onto a “report” page within a workbook. The entire “report” page is then copied and moved into a new workbook. That allows me to have a stand alone, exported file outside of my main data entry tool. When you copy an entire page to a new workbook, Excel imposes a 255 character limit. Thus, I see truncated responses on the exported spreadsheet only.

Thanks!!
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Let's say you go to the last line and write something like
=SUMPRODUCT(--(LEN(A1:A:65500)>255))
I'm having trouble getting this to work.

I have changed the range to suit but get a #Value error. I'm guessing that the LEN function does not work over a range or should I be doing something with the "--" as I do not recognise this.
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F11)
Code:
Sub over255()
Dim x, y, a, b, d, f as integer
dim c, e as string
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
For a = 1 To x
For b = 1 To y
c = Cells(a, b)
d = Len(c)
e = Cells(a, b).Formula
f = Len(e)
If d > 255 Or f > 255 Then
Cells(a, b).Interior.ColorIndex = 6
End If
Next b
Next a
End Sub
Run the macro. it will highlight cells yellow if they contain a text or formula greater than 255 characters.
Ravi
 
Upvote 0

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Hi
Paste the following codes in the macro window ( Alt F11)
Code:
Sub over255()
Dim x, y, a, b, d, f as integer
dim c, e as string
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
For a = 1 To x
For b = 1 To y
c = Cells(a, b)
d = Len(c)
e = Cells(a, b).Formula
f = Len(e)
If d > 255 Or f > 255 Then
Cells(a, b).Interior.ColorIndex = 6
End If
Next b
Next a
End Sub
Run the macro. it will highlight cells yellow if they contain a text or formula greater than 255 characters.
Ravi
Great - that worked a treat and it was a text cell!

Thanks very much.
 
Upvote 0

Forum statistics

Threads
1,186,803
Messages
5,959,870
Members
438,453
Latest member
NRG909

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top