Get rid of CHAR(12)

[Guest]

Hi All.......

I have imported a flat text file, all in column A, and some rows contain the
little square symbol I think is the non-printing CHAR(12) character. How can
I delete all the rows that contain this symbol, please?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Fredrik Wahlgren]

Hi All.......

I have imported a flat text file, all in column A, and some rows contain the
little square symbol I think is the non-printing CHAR(12) character. How can
I delete all the rows that contain this symbol, please?

TIA
Vaya con Dios,
Chuck, CABGx3

You can get rid of these characters by using the CLEAN function
http://www.techonthenet.com/excel/formulas/clean.htm

/Fredrik

 

[Bob Phillips]

Hi Chuck,

Dim i As Long
Dim cell As Range

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(i, "A").Value, Chr(12)) > 0 Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Fredrik Wahlgren]

Thanks Fredrik, that does do the job........but how might I write that into
code?

Chuck

As the example shows, you can move your data to a different column or sheet.

/Fredrik

 

[Guest]

Thanks Fredrik, that does do the job........but how might I write that into
code?

Chuck

 

[Tom Ogilvy]

You want to delete the row where a cell contains chr(12) ?

Sub DeleteRow()
Dim rng As Range, rng1 As Range
With Cells
Set rng = .Find(Chr(12))
If Not rng Is Nothing Then
Set rng1 = rng
Do
Set rng1 = Union(rng1, rng)
Set rng = .FindNext(rng)
Loop While Intersect(rng, rng1) Is Nothing
End If
If Not rng1 Is Nothing Then _
rng1.EntireRow.Delete
End With
End Sub

 

[CLR]

I see..........ok, thank you for your time and suggestion.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

That does the job for me very very nicely Bob........thank you most
kindly........maybe some day I'll understand how these things
work........and then again, maybe not<g>

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Another great solution that works excellently.........Thank you very much
Tom........you always hit the spot.

Vaya con Dios,
Chuck, CABGx3