# Circumventing silly 255 char limit when copying entire sheet

#### TryingMyBest

##### New Member
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
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.

### 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
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

#### grd

##### Board Regular
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.

Replies
2
Views
1K
Replies
0
Views
137
Replies
3
Views
786
Replies
1
Views
827
Replies
1
Views
3K

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.

### Which adblocker are you using?

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

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