Would love to write macro to delete any row that DOESN'T containspecific text..

[John]

* 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)

 

[Don Guillett]

Just use data>filter>autofilter>filter with "contains">old
stuff>delete>>>>
record a macro if desired

 

[John]

Just use data>filter>autofilter>filter with "contains">old
stuff>delete>>>>
record a macro if desired

On Jan 25, 10:44 am, John <[email protected]> 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

 

[GS]

John has brought this to us :

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?

 

[Don Guillett]

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

 

[Vacuum Sealed]

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.

 

[Don Guillett]

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