VBA Filter out unwanted records
 

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
--
James

VBA Filter out unwanted records
 

Hi,

A couple of points:-

1. This could be achieved quite easily without a macro.
2. In your question header you say 'Filter out'
3. In your message body you say 'Delete'

This 'Deletes' so beware

Sub stantial()
Dim MyRange, CopyRange As Range
Dim lastrow As Long
Set sHt = Sheets("Sheet1")
lastrow = sHt.Cells(Cells.Rows.Count, "BE").End(xlUp).Row
Set MyRange = sHt.Range("BE2:BE" & lastrow)
For Each c In MyRange
If Not InStr(1, c.Value, "U", vbTextCompare) = 1 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If

End Sub


--
Mike

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


"James C" wrote:

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
--
James

VBA Filter out unwanted records
 

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.

--
James


"Mike H" wrote:

Hi,

A couple of points:-

1. This could be achieved quite easily without a macro.
2. In your question header you say 'Filter out'
3. In your message body you say 'Delete'

This 'Deletes' so beware

Sub stantial()
Dim MyRange, CopyRange As Range
Dim lastrow As Long
Set sHt = Sheets("Sheet1")
lastrow = sHt.Cells(Cells.Rows.Count, "BE").End(xlUp).Row
Set MyRange = sHt.Range("BE2:BE" & lastrow)
For Each c In MyRange
If Not InStr(1, c.Value, "U", vbTextCompare) = 1 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If

End Sub


--
Mike

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


"James C" wrote:

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
--
James

VBA Filter out unwanted records
 

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.


"James C" wrote:

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.

--
James


"Mike H" wrote:

Hi,

A couple of points:-

1. This could be achieved quite easily without a macro.
2. In your question header you say 'Filter out'
3. In your message body you say 'Delete'

This 'Deletes' so beware

Sub stantial()
Dim MyRange, CopyRange As Range
Dim lastrow As Long
Set sHt = Sheets("Sheet1")
lastrow = sHt.Cells(Cells.Rows.Count, "BE").End(xlUp).Row
Set MyRange = sHt.Range("BE2:BE" & lastrow)
For Each c In MyRange
If Not InStr(1, c.Value, "U", vbTextCompare) = 1 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If

End Sub


--
Mike

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


"James C" wrote:

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
--
James

VBA Filter out unwanted records
 

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

On Tue, 26 Jan 2010 04:16:15 -0800, James C
wrote:

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