Delete row based on a letter in cell

[Hawke]

I need to delete multiple rows based on a letter in a product code.
eg

cell A1 Cell B1 Cell c1
B2541U Any Descp 12.60

entry in colum A that ends with a U I need to delet the row. The entry in
col A is not the same amount of char. in each case but the U is always at
the end.

How can I create a macro or Vb code to handle this problem.
Any help appreciated..
Roger

 

[Dave Peterson]

You can apply Data|filter|autofilter.

then use the dropdown on column A and choose custom.
then Ends with U
then delete those visible rows
Data|filter|autofilter (to remove the filter)

 

[Dave Peterson]

And if you needed a macro, you could record one when you did it manually.

(I didn't notice that last part--and we're in .worksheet.functions)

 

[J.E. McGimpsey]

One way:

Public Sub DeleteEndU()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
If Right(cell.Text, 1) = "U" Then
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange, cell)
End If
End If
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

 

[Hawke]

Thanks for all who responded, your solutions have help'd me greatly.
This group is awealth of information and educations
Tks again

Roger

 

[Hawke]

The VB code sound sgreat, however how is it executed, by a macro? or is it
done automatically

Roger

 

[J.E. McGimpsey]

It's a macro, so can be run from the Tools/Macro/Macros dialog,
given a keyboard shortcut or attached to a toolbar button.

I assumed you would not want to automate it. If you do (e.g., you
paste data frequently), put this in the Worksheet code module
(right-click on the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
DeleteEndUI
End Sub

Note that this may be a lot of overhead...