Delete row question

[jamespatrick]

Hello,

Any help with this question would be greatly appreciated as I am a bit of an excel amateur!

Is it possible to delete rows that do not match a stated reference number? Basically I have 2 columns, ref no. and name and I only want to show names that match a reference number. Here is the difficult part the ref no. is a 4 digit number followed by a letter and it is the letter that I need to match.

eg : Ref No. Name
1232A James
4242P John
2434C Steve
5453P Jack

I only want to show ref no.s ending in P which is John and jack. Is it possible to only show these rows?

Thanks

James

 

[Rick Rothstein]

You could add a helper column and put this formula in it...

=RIGHT(A2)

and copy it down, then AutoFilter on that helper column.

 

[L. Howard]

Hi James,
Try this out on a sample of your data.

Option Explicit
Sub PKeeper()
Dim LRow1 As Long
Dim rngC As Range
With Sheets("Sheet1")
  LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
    For Each rngC In .Range("B2:B" & LRow1)
      If rngC.Value = Left$(rngC, 4) <> "P" Then
        rngC.EntireRow.Delete
      End If
    Next
End With
End Sub

Regards,
Howard

 

[jamespatrick]

Great,

Thanks for the solutions, I love the simplicity of the helper column that works great and I didn't realize about the right command. I will try out the code and let you know how I get on.

Thanks again

James

 

[L. Howard]

You are welcome, and Rick's advice is pretty much a gold standard. Can't go wrong there.

Howard