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

  • Thread starter Thread starter John
  • Start date Start date
J

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)
 
Just use data>filter>autofilter>filter with "contains">old
stuff>delete>>>>
record a macro if desired
 
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:
Click to expand...

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
 
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
Click to expand...

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?
 
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
Click to expand...


does NOT
<> contain
 
does NOT
<> contain
Click to expand...

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.
 
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.
Click to expand...

Looping is slow compared to filtering
 
Back
Top