cell with only a single quote

G

gary

I need to delete rows with cells containing only single
quotation marks. How can I replace the single quotation
mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find
them? Or is there a better way?
 
W

William

Hi gary

Assuming you want to delete all rows where there is a " ' " in column A,
then this may work......


Sub test()
Dim r As Range, c As Range, rr As Range
Set r = _
Columns("A:A").SpecialCells(xlCellTypeConstants, 23)
Set rr = Nothing
For Each c In r
If Len(c) = 0 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(c, rr)
End If
End If
Next c
If Not rr Is Nothing Then _
rr.EntireRow.Delete Shift:=xlUp
End Sub

--
XL2002
Regards

William

[email protected]

| I need to delete rows with cells containing only single
| quotation marks. How can I replace the single quotation
| mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find
| them? Or is there a better way?
|
|
 
R

Ragdyer

Select the column that contains these cells and simply try:
<Data> <TextToColumns> <Finish>.
 
Top