Circumventing silly 255 char limit when copying entire sheet

[TryingMyBest]

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]

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.

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

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]

Hi
Paste the following codes in the macro window ( Alt F11)

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.