How to delete rows based on cell value

[mesje]

Hello,

I have a massive spreadsheet (ca.110 000 rows :/ [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.

Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:
"mytextascriteria*", where * somehow stands for the rest of the string.
Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.

I found the thread about deleting entire row based on a cell value here:
http://www.mrexcel.com/forum/showthread.php?t=78407
but I couldn't quite work out how to apply it in my scenario.... :/


Any help would be much appreciated...
Otherwise is completely pointless to do it manualy with these amout of records.

Take care and thank you for any hints and solutions,

mesje

 

[Richard Schollar]

Hi

This is one way:

Sub Del_Rows()
Dim i As Long
Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count,"A").End(xlUp).Row

For i = lngLastRow to 2 Step -1
  If Left(Cells(i,1).Value,16) = "mytextascriteria" Then
     Cells(i,1).EntireRow.Delete
  End If
Next i
End Sub

Amend the textual criteria and the length of this string fragment to suit (relevant bits of code in red).

 

[mesje]

Wow.. many thans for extremely quick reply!

hmm... I've amended those two criteria, and also I changed the column (to E - that's where my data was).
Code runs without errors, but no rows have been deleted.

And I just noticed that I wrongly ask about deleting cells; but looking at your code you got it right anyway.

Any ideas ?

 

[Michael M]

Richards Code should work for you
Make sure the 16 leftmost characters are the ones you mentioned....no spaces / commas / quotes / etc.
Otherwise post a small sample of your data.

 

[Richard Schollar]

Also, the code as written is case-sensitive (so the first 16 characters must exactly match - "StoP ME Doing THIs" is not the same as "STOP ME DOING THIS").

Probably best to try this amended version - simply type in your identifier text where indicated and then run the code:

Sub Del_Rows()
Const MYTEXT = "mytextascriteria" '=== type in your text between the quotes ""

Dim i As Long
Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count,"E").End(xlUp).Row

For i = lngLastRow to 2 Step -1
  If UCase(Left(Cells(i,"E").Value,Len(MYTEXT))) = UCase(MYTEXT) Then
     Cells(i,1).EntireRow.Delete
  End If
Next i
End Sub

 

[mesje]

Hi, many thanks for solution.
I've tried a few times and finally found what was missing.
Code worked very well.

Many thanks for your remarkable help!

Take care,
mesje