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

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

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

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

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

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.

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