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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Steph" wrote in message
om...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

Steph wrote:

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.

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
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Beto wrote:

Steph wrote:

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.

Do'h, I did it the other way around... I'll fix it now.


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


--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Beto wrote:

Beto wrote:

Steph wrote:

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.


Do'h, I did it the other way around... I'll fix it now.

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!
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

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


(Steph) wrote in message . com...
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

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


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Steph" wrote in message
om...
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


(Steph) wrote in message
. com...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
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


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Steph" wrote in message
om...
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


(Steph) wrote in message
. com...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

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


(Steph) wrote in message . com...
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


(Steph) wrote in message . com...
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