Delete Row with string containing special character

**DoodlesMama** · 02-24-2014, 01:26 PM

I am trying to delete rows that contain text with a special character in column O. The text I am searching for contains PO# and will look something like this PO#B7605887-1, so I need it to be PO#* so it catches the text if it contains anything after the special character.

The code I created below is not working. What am I doing wrong?

Sub Delete_POnums()
    Dim k As Long 'PO#
           On Error GoTo 0

        With ThisWorkbook.Sheets("Call Data").Range("O:O")
                For k = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
                        With .Cells(k, 1)
                    If CStr(.Value) Like "PO" & Chr(35) & "*" Then .EntireRow.Delete Shift:=xlUp
                End With
            Next k
        End With

End Sub

**patel45** · 02-24-2014, 01:58 PM

try this change

For k = .Cells(.Rows.Count, "O").End(xlUp).Row To 1 Step -1

**Solus Rankin** · 02-24-2014, 02:28 PM

Do any values contain the letters "PO" without containing the symbol #?

maybe

Sub Delete_POnums()
Dim k As Long 'PO#

With ThisWorkbook.Sheets("Call Data")
    For k = .Cells(.Rows.Count, 15).End(xlUp).Row To 1 Step -1
        If .Cells(k, 15).Value Like "PO*" Then .Cells(k, 1).EntireRow.Delete Shift:=xlUp
    Next k
End With
End Sub

**DoodlesMama** · 02-24-2014, 03:22 PM

Solus, if I add the * after "PO", then it would delete also delete rows with words that start with a PO, like possible or potential, etc. I need it to delete rows with the phrase "PO#".

**HaHoBe** · 02-24-2014, 03:30 PM

Hi, John,

your code

Range("O" & Rows.count).End(3)(1).Row

isn´t qualfied for Sheets("Call Data") but should refer to the ActiveSheet instead.

Ciao,
Holger

**JOHN H. DAVIS** · 02-24-2014, 03:36 PM

Originally Posted by HaHoBe

Hi, John,

your code

Range("O" & Rows.count).End(3)(1).Row

isn´t qualfied for Sheets("Call Data") but should refer to the ActiveSheet instead.

Ciao,
Holger

"Call Data" is the activesheet? Isn't it?

**HaHoBe** · 02-24-2014, 03:46 PM

Hi, John,

I don´t know to be honest. But if I see a code like

With ThisWorkbook.Sheets("Call Data").Range("O:O")

I´d go the way to use

Sub DoodlesMama()
With Sheets("Call Data")
  .Range("O1:O" & .Range("O" & Rows.Count).End(3)(1).Row).AutoFilter 1, "=PO#*"
  .Range("O1:O" & .Range("O" & Rows.Count).End(3)(1).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  .AutoFilterMode = False
End With
End Sub

to avoid any possible other activesheet or

Sub DoodlesMama2()
With Sheets("Call Data")
  .Range("O1", .Range("O" & Rows.Count).End(3)(1)).AutoFilter 1, "=PO#*"
  .Range("O1", .Range("O" & Rows.Count).End(3)(1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  .AutoFilterMode = False
End With
End Sub

Ciao,
Holger

**JOHN H. DAVIS** · 02-24-2014, 02:47 PM

Another:

Sub DoodlesMama()
Sheets("Call Data").Range("O1:O" & Range("O" & Rows.count).End(3)(1).Row).AutoFilter 1, "=*PO#*"
Sheets("Call Data").Range("O1:O" & Range("O" & Rows.count).End(3)(1).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Sheets("Call Data").AutoFilterMode = False
End Sub

**Solus Rankin** · 02-24-2014, 05:55 PM

Sorry. This should fix that problem:

Sub Delete_POnums()
Dim k As Long 'PO#

With ThisWorkbook.Sheets("Call Data")
    For k = .Cells(.Rows.Count, 15).End(xlUp).Row To 1 Step -1
        If .Cells(k, 15).Value Like "PO?#*" Then .Cells(k, 1).EntireRow.Delete Shift:=xlUp
    Next k
End With
End Sub

**Xx7** · 02-24-2014, 06:02 PM

Try this...

Sub tester()

Dim ws As Worksheet
Set ws = Sheets("Call Data")

Dim r1 As Long  'Row to delete...
Dim s1 As Long  'Sum of deletions...
Dim i As Long

s1 = 0

Do While 1
    On Error GoTo ErrorHandler
        r1 = ws.Columns("O:O").Find(What:="PO#", LookIn:=xlValues).Row
    If r1 > 0 Then
        s1 = s1 + 1
    End If
    ws.Rows(r1 & ":" & r1).Delete
    i = i + 1
Loop


ErrorHandler:
    MsgBox s1 & " rows removed!"
    Exit Sub

End Sub