Square replace 3

[marcoman44]

In excel I have an imported a report that produced square symbols. The problem is that they cannot be replaced trough Edit/Find/Replace. I think they might be some type of carriage returns from the other report. I cannot do a sort and delete rows becuase of the format of the data. Any help is appreciated.

Thanks,

 

[longhair]

marcoman44,
i adjusted the code in a slightly later post. i forgot to increment the counter (x). after the 'do until' line put:

x = x + 1

 

[longhair]

TonyJollans,
that's why i asked marcoman44 to make sure he runs the test with the msgbox. if it returns asc(12) then we can search and act based upon that. like i said my guess is that the original file came from a *nix box. asc(12) is a linefeed, if i remember correct, on *nix systems indicating end of page.
regards,
longhair

 

[sawedoff]

In this length of time and effort, I could have manually deleted all the offending squares.

Sawedoff

 

[marcoman44]

there are four 12 files each with 65,000 records.

 

[longhair]

sawedoff,

perhaps.
no intent to flame here so please do not take in a bad way- but myself and others would not have learned anything either.
regards,
longhair

 

[marcoman44]

longhair,

I put the code in and it did not work.

Public Sub Test()


Dim x As Integer
Dim y
y = AscW(12)
Do Until x = 20000 'or however many rows there are total
x = x + 1
If ActiveCell.Value = y Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate 'go down to next line
End If
Loop

End Sub

 

[longhair]

marcoman44,
did the curser move at all?
did you receive any errors?
are you in the column with the asc(12) character?
while you have the vba editor open, you can press f8 to step through the code and see what is in each var.
regards,
longhair

 

[marcoman44]

longhair,

would you like me to email you a example of what I am doing?

Thanks,

 

[longhair]

marcoman44,
not in the habbit of posting any of may emails.
just post some samples. i'll copy and paste into a spreadsheet. does not have to be exaclty like you have, just the offending column, some good data and some bad data.
regards,
longhair

 

[marcoman44]

The problem is that the Character will not show up when I select Submit Post. Is there another way to send this out? I think it needs to be in the Excel format that it is in.

 

[earthandfire]

marcoman44, while longhair's solution would in theory at least (as long as the offending character is an ASCII 12 (form feed)) solve your problem, it seems not to have done so yet.

Have you tried my suggestion. If nothing else, it should identify the offending character and give us an alternative strating point.

Depending on your current system codepage, the square could have any value below 32 (except probably 9,10 or 13) or above 127.

 

[longhair]

marcoman44,
set up an alias on my email account will take a few for it to complete. will post back w/email when it's ready.
regards,
longhair

 

[longhair]

marcoman44,
can send to
0ne7ime_only at mindspring.com

make sure to copy & paste since some are numbers, remove & replace the obvious
regards,
longhair

 

[marcoman44]

sent to 0ne7ime_only@mindspring.com

 

[longhair]

marcoman44,
here is the corrected code (worked on your sample):

Dim x As Integer
Dim y
x = 0
Do Until x = 150
x = x + 1
If ActiveCell.Value <> "" Then
y = Asc(ActiveCell.Value)
If y = 12 Then
 ActiveCell.Value = ""
End If
End If
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
Loop

just change the 'do until' to match your total # of rows.
hth
regards,
longhair

 

[marcoman44]

It worked,

Thanks to everyone for the help. Especially longahair.