Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete unwanted records | Excel Discussion (Misc queries) | |||
Number of records after filter | Excel Worksheet Functions | |||
Filter Unique Records | Excel Discussion (Misc queries) | |||
How can I always get subtotals (4 of 12 records)when I filter a sh | Excel Discussion (Misc queries) | |||
Why does it say "Filter mode" instead of "X of Y records"? | Excel Worksheet Functions |