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

"CLR" wrote in message
...
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

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)


"CLR" wrote in message
...
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

"CLR" wrote in message
...
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

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

Chuck


"Fredrik Wahlgren" wrote:


"CLR" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Fredrik, that does do the job........but how might I write that
into
code?

Chuck


"Fredrik Wahlgren" wrote:


"CLR" wrote in message
...
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

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

Vaya con Dios,
Chuck, CABGx3


"Fredrik Wahlgren" wrote in message
...

"CLR" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
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)


"CLR" wrote in message
...
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

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

Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Fredrik, that does do the job........but how might I write that
into
code?

Chuck


"Fredrik Wahlgren" wrote:


"CLR" wrote in message
...
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