Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
* I would like it to ignore row 1, because it is the header row.
* Then I want to go through a massive Excel document and delete each row that doesn't contain a specific string of text (which happens to be in column 1). FOR EXAMPLE: -- if a row has MyOldStuff in column 1 then delete the entire row, -- if a row starts with MyNewStuff then keep the entire row (i.e. do nothing and move on to the next row) ------ I hope that this is the right place to ask, and I'd like to thank anyone who can guide me in advance! This is a very long spreadsheet, with thousands of rows |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44*am, John wrote: * I would like it to ignore row 1, because it is the header row. * Then I want to go through a massive Excel document and delete each row that doesn't contain a specific string of text (which happens to be in column 1). FOR EXAMPLE: -- if a row has MyOldStuff in column 1 *then delete the entire row, -- if a row starts with MyNewStuff then keep the entire row (i.e. do nothing and move on to the next row) ------ I hope that this is the right place to ask, and I'd like to thank anyone who can guide me in advance! This is a very long spreadsheet, with thousands of rows |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
On Jan 25, 4:51*pm, Don Guillett wrote:
Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44*am, John wrote: Ah, but once the filter is applied it shows me the rows of data that I want to keep, not delete. Hmm... I wonder if there's a way to do an inverse selection. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T contain specific text..
John has brought this to us :
On Jan 25, 4:51*pm, Don Guillett wrote: Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44*am, John wrote: Ah, but once the filter is applied it shows me the rows of data that I want to keep, not delete. Hmm... I wonder if there's a way to do an inverse selection. Thanks would that be because (if you followed Don's instructions) the rows you wanted to delete are gone, possibly, and so the rows you wanted to keep is all that's left? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
On Jan 25, 10:55*am, John wrote:
On Jan 25, 4:51*pm, Don Guillett wrote: Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44*am, John wrote: Ah, but once the filter is applied *it shows me the rows of data that I want to keep, not delete. Hmm... I wonder if there's a way to do an inverse selection. Thanks does NOT < contain |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
On 26/01/2012 8:45 AM, Don Guillett wrote:
On Jan 25, 10:55 am, wrote: On Jan 25, 4:51 pm, Don wrote: Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44 am, wrote: Ah, but once the filter is applied it shows me the rows of data that I want to keep, not delete. Hmm... I wonder if there's a way to do an inverse selection. Thanks does NOT < contain Hi John Try this ( tested ) Sub Remove_Unwanted() Dim Firstrow As Long, Lastrow As Long, Lrow As Long With Sheets("Sheet1") 'rename sheet to match yours ..Select Firstrow = .UsedRange.Cells(2).Row 'starts at row(2) assumes top row(1) is header row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value < "MyNewStuff" Then .EntireRow.Delete End If End With Next Lrow End With End Sub HTH Mick. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Would love to write macro to delete any row that DOESN'T containspecific text..
On Jan 25, 7:41*pm, Vacuum Sealed wrote:
On 26/01/2012 8:45 AM, Don Guillett wrote: On Jan 25, 10:55 am, *wrote: On Jan 25, 4:51 pm, Don *wrote: Just use datafilterautofilterfilter with "contains"old stuffdelete record a macro if desired On Jan 25, 10:44 am, *wrote: Ah, but once the filter is applied *it shows me the rows of data that I want to keep, not delete. Hmm... I wonder if there's a way to do an inverse selection. Thanks does NOT < *contain Hi John Try this ( tested ) Sub Remove_Unwanted() Dim Firstrow As Long, Lastrow As Long, Lrow As Long With Sheets("Sheet1") 'rename sheet to match yours .Select Firstrow = .UsedRange.Cells(2).Row 'starts at row(2) assumes top row(1) is header row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value < "MyNewStuff" Then .EntireRow.Delete End If End With Next Lrow End With End Sub HTH Mick. Looping is slow compared to filtering |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Write and delete value in registry | Excel Programming | |||
how to write to macro to only delete rows under certain conditions | Excel Programming | |||
Delete rows from a protected sheet (write macro) | Excel Programming | |||
How do I write a macro to delete all rows from the first empty ro. | Excel Programming | |||
How do I write a macro in XL that will delete 1st row and save th. | Excel Programming |