Delete row based on a letter in cell

  • Thread starter Thread starter Hawke
  • Start date Start date
H

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
 
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)
 
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)
 
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
 
Thanks for all who responded, your solutions have help'd me greatly.
This group is awealth of information and educations
Tks again

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

Roger
 
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...
 
Back
Top