search for a text in a Column and then delete 1

[peach255]

I am not too familiar w/ looping and searching for text in Excel cells. I would like to look at each cells in Column E, and everytime it sees the word "Standard", delete the whole row. How can I do this? Thank you!

 

[SkipVought]

Hi

Sub DeleteRows()
    For lRow = Range(Cells(1, 5), Cells(Cells.Rows.Count, 5).End(xlUp)).Rows.Count To 1 Step -1
        If Cells(lRow, 5).Value = "Standard" Then Cells(lRow, 5).EntireRow.Delete shift:=xlUp
    Next
End Sub

Be sure to save your workbook before running this macro.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

peach255: If you can't get Skip's code to do what you want, try this variation:
[blue]

Sub DeleteStandardRows()
Dim nRow As Long

  With Intersect(ActiveSheet.UsedRange, Range("E:E"))
    For nRow = .Rows.Count To 1 Step -1
      If LCase(.Cells(nRow, 1)) = "standard" Then
        .Cells(nRow, 1).EntireRow.Delete
        nRow = nRow + 1
      End If
    Next nRow
  End With
End Sub

[/color]

Or change Skip's code to use this (Assumes your data starts on row 1):

    For lRow = Cells(1, 5).End(xlDown).Row To 1 Step -1

 

[Zathras]

Skip, sorry - I spoke too soon. The reason I couldn't get your code to work the first time is that I hadn't capitalized "Standard" in my test data. Your version of the "for" statement works ok (as long as the case-sensitive test is sufficient)

 

[peach255]

Thanks for both of your help! It worked! Thank you!

 

[SkipVought]

Zathras,

I am always looking for good implicit solution strategies and I really like the use of Intersect in yours!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[velveeta]

I am trying to use Skip's and Zathras code for text I am bringing into Excel (very useful); however, I can't get the code to work. I am bringing in through Excel's clipboard 12 blocks of text, that are each between 9-11 lines. I am trying to subtract all but 12 lines, to extract what I need, by using the code. Everything that's brought in goes to column A, so of course, I've changed E:E to A:A. And, I've changed the word "Standard" to "Tabulation". I think everything else should work. So far I can't get the code to delete even one line (12 times). Any ideas on what I might be doing wrong. Like Peach, I am new to trying to loop through Excel text. Sorry for the newbieness.

Thank you!

 

[Zathras]

...Everything that's brought in goes to column A...

Do you mean that nothing is in column B? Nothing at all?

Then does the text in column A consist of a long string of text of which "Tabulation" is only a part?

If that is the case, then try this variation:
[blue]

Sub DeleteTabulationRows()
Dim nRow As Long
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Range("A:A"))
    For nRow = .Rows.Count To 1 Step -1
      If InStr(LCase(.Cells(nRow, 1)), "tabulation") > 0 Then
        .Cells(nRow, 1).EntireRow.Delete
        nRow = nRow + 1
      End If
    Next nRow
  End With
  Application.ScreenUpdating = True
End Sub

[/color]

 

[velveeta]

Zathra - you are awesome!

That code worked perfectly - and you were right, I actually did have some other information in the other columns, but I was able to get rid of that by repeating the same code!

Now that you've made my life hugely simpler, all I need to do is add a blank row every time there is a "0" or a "2" in column A, and delete rows D,E,G,H,,I and J, and the report will be pretty much done.

I will be looking for that info in other threads, but if anyone wants to point to those threads, that too would be appreciated.........

thanks a bunch!