text file page breaks

[ermccarthy]

I have a macro that uploads a .prn file into my workbook, and then formulas run off of that uploaded data. My problem is sometimes the text file puts in page breaks which appear as small rectangles after the number (at the end of a line). With those there, my formulas do not work. Is there anyway to make these go away either with a command in the macro during the upload process, or by running a macro to "search and destroy" these pesky little boxes....(PS the boxes are always there in the file, but sometimes that fall in areas that don't bother me, like after a label, so they are not always in the same place)

Any ideas??

_________________
Russell

[ This Message was edited by: ermccarthy on 2002-02-21 11:06 ]

[Mark W.]

It might be easiest to replace these characters using a system editor such as EditPlus or BBEdit. Using Excel's CODE function can you tell us what the ASCII code is for these characters?

[ermccarthy]

ok......so I copied the page break into a cell and type in the next cell: =Code(A1) this returned a value of 12.

Therefore how do I tell it to search the entire area looking for this character and delete it, using this value??

[Russell Hauf]

Are you using Excel 2000? If so, try this (you must have the range that may contain page breaks highlighted):

Sub RemovePageBreaks()
    Dim cl As Range

    For Each cl In Selection
        If InStr(cl.Text, Chr(12)) Then
            cl = Replace(cl.Text, Chr(12), "")
        End If
    Next cl
End Sub

If you're not using XL 2K, let me know. Otherwise, hope this helps!

-Russell

[Mark W.]

On 2002-02-21 11:05, ermccarthy wrote:
I have a macro that uploads a .prn file into my workbook, and then formulas run off of that uploaded data. My problem is sometimes the text file puts in page breaks which appear as small rectangles after the number (at the end of a line). With those there, my formulas do not work. Is there anyway to make these go away either with a command in the macro during the upload process, or by running a macro to "search and destroy" these pesky little boxes....(PS the boxes are always there in the file, but sometimes that fall in areas that don't bother me, like after a label, so they are not always in the same place)

Any ideas??

_________________
Russell

[ This Message was edited by: ermccarthy on 2002-02-21 11:06 ]

Or, simply...

Sub Macro1()
Selection.Replace What:=Chr(12), Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub