Delete rows that DO NOT contain certain text

  • Thread starter Thread starter Steph
  • Start date Start date
S

Steph

Hi. I have a file that has several sheets, and each sheet is about
40k rows long. It was an import from a text file. In column B is a
long text string. I need to delete all rows in every sheet that DO
NOT contain the word "logoff" OR "timeout" OR "logon" somewhere in the
cell in column B.

I am basically trying to delete the stuff I don't need so the file is
more manageable and can be worked with. Thanks in advance for your
help!

Steph
 
Hi, it is simple, but I hope, it can work.


Sub test()
Dim Cell As Range
For Each Cell In Range("B1:B100")
Cell.Select
With Selection
Set c = .Find("login", LookIn:=xlValues)
Set d = .Find("logout", LookIn:=xlValues)
If c Is Nothing Or d Is Nothing Then
ActiveCell.EntireRow.Delete
End If
End With
Next
End Su
 
Try this:-

Sub DelRowsNotCont()
Dim x As Long
Dim c As Range


Application.ScreenUpdating = False

For x = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
With Intersect(Range("B:B"), ActiveSheet.UsedRange.Rows(x))
Set c = .Find("logoff", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("timeout", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("logon", LookIn:=xlValues)
If c Is Nothing Then

.EntireRow.Delete
End If
End If
End If
End With
Next x

Application.ScreenUpdating = True

End Sub
 
Steph said:
Hi. I have a file that has several sheets, and each sheet is about
40k rows long. It was an import from a text file. In column B is a
long text string. I need to delete all rows in every sheet that DO
NOT contain the word "logoff" OR "timeout" OR "logon" somewhere in the
cell in column B.
Click to expand...

Hi,

Sub EraseCertainRows()
Application.ScreenUpdating = False
For Each cell In Range("B:B")
If InStr(1, cell.Value, "logoff") <> 0 Or _
InStr(1, cell.Value, "logon") <> 0 Or _
InStr(1, cell.Value, "timeout") <> 0 Then
cell.EntireRow.Delete
End If
Next cell
Application.ScreenUpdating = True
End Sub

This will perform the task. I believe someone else will come up with
better code maybe using Find, but this is a first approach.

Regards,
 
Beto said:
Do'h, I did it the other way around... I'll fix it now.
Click to expand...

Sub EraseCertainRows()
Application.ScreenUpdating = False
For Each cell In Range("B:B")
If InStr(1, cell.Value, "logoff") = 0 And _
InStr(1, cell.Value, "logon") = 0 And _
InStr(1, cell.Value, "timeout") = 0 Then
cell.EntireRow.Delete
End If
Next cell
Application.ScreenUpdating = True
End Sub

Fixed!
 
Thanks guys! I really appreciate all of your help. One follow up
question - I ran this code, and it takes forever! I deleted all sheets in
the file expect for 2. Each of the 2 sheets has 38k rows. After 15
minutes, I cancelled the execution. Is my machine really slow, or is there
a quicker way to achieve this? Thanks again!

Steph
 
What form does the data take - Are there lots of formula and links in there that
will recalculate every time and slow it down. If so then maybe switching to
manual calculation will help. Did you turn off screenupdating in whichever
piece of code you used.

If you are not sure whether it has just hung or it is catually still going, then
you can use the Application.Statusbar code to give you a status of how far
through it is. For example, in the code I gave you:-

Sub DelRowsNotCont()
Dim x As Long
Dim c As Range


Application.ScreenUpdating = False

For x = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
With Intersect(Range("B:B"), ActiveSheet.UsedRange.Rows(x))
Set c = .Find("logoff", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("timeout", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("logon", LookIn:=xlValues)
If c Is Nothing Then

.EntireRow.Delete
End If
End If
End If
End With
Application.Statusbar = "Row " & x & " currently being processed"
Next x

Application.ScreenUpdating = True
Application.Statusbar = False ' Clears the messages from the statusbar

End Sub
 
Oops - Should note that the row in the being referred to in the line
Application.Statusbar = "Row " & x & " currently being processed", is not
necessarily the actual row for that number. If you have blank rows at the
beginning of your data then they will not be counted in the
ActiveSheet.UsedRange.Rows.Count bit, and it will be related to the row's
position in the UsedRange collection of rows, so for example Row 1 in the
UsedRange could actually be Row 222 on the spreadsheet.
 
Hi Ken. Thanks for your response. The form of the data was text,
that was imported into Excel. So there are no formulas in their
whatsoever. Just 40K rows on each sheet.

I think I conceptually have an idea of how to speed things up. I
posted a new thread to the newsgroup called 'Combine 2 macros'.
Rather than importing multiple 40k sheets, and THEN running your
delete code on all of them, I was thinking of importing 1 sheet,
running your code to thin it out, and the looping to open 1 more
sheet, then run your code on that sheet, etc....

I posted both snippets of code in the hopes that someone would be able
to combine them into a loop. No responses yet.... :(

Thanks Ken!

PS - The application status bar update was a nice touch!!

Steph
 
Back
Top