VBA Filter out unwanted records

J

James C

I have a list in a worksheet (named Filter) which has a number of columns
with a headings row . In Column BE (BE1 has the heading Manage1) there are
text data entires which have prefixes with a letter followed by a number. I.e
E10, V19,U23.
The list is always changing by adding more rows, so the range for column BE1
is varaiable.
I would like a VBA statement that will delete all the records in the list
where the records in column BE does not have the prefix U, but must leave the
list with it's column headings
Thanks
 
J

James C

Mike thanks for your quick response.
I understand that this could be done with out a macro but it fits in within
another macro under construction.
Sorry to confuse I did mean delete and not filter, shall be careful next
time I ask a question. Good point.
The VBA works great just what I wanted, many thanks.
 
M

Mike H

Glad I could help
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
G

Gord Dibben

Your column has changed from your original post and you have added the
caveat about retaining titles in row 1.

Best to stay with the original thread for continuity of replies.

Try this...........

Option Compare Text
Sub Delete_NonU_Rows()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 2
LastRow = Cells(Rows.Count, "BE").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "BE").Value, 1) <> "U" Then
Rows(iRow).EntireRow.Delete
End If
Next
End Sub


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top