speeding up this code
 

Hi All,

I've got a directory listing dumped into an Excel sheet (created from the
command line with 'dir \s dir.txt'), which i am trying to get in to some
sort of order.

I've created the code below to check for certain text within cells and if
present, delete the containing row. The code works well enough, but as i
have 60,000 rows it is taking a VERY LONG time. is there any way to
significantly speed up this task?

'=========
Sub rowdel()

Dim Tcell As Range, x, y
x = 0
y = 0

For Each Tcell In Selection

If InStr(1, Tcell.Value, "<DIR .") 0 Then
Tcell.Rows.Delete
x = x + 1
End If

y = y + 1
Application.StatusBar = y

Next Tcell

MsgBox x & " lines removed"
Application.StatusBar = False

End Sub

'=========

Thanks for any help,

Tim

speeding up this code
 

Sorry for replying to my own post, but i just turned of screenupdating and
it speeded everything up nicely ;o)

"Tim" <tmarsh-trousers-@-take off my trousers to reply-blueyonder.co.uk
wrote in message ...
Hi All,

I've got a directory listing dumped into an Excel sheet (created from the
command line with 'dir \s dir.txt'), which i am trying to get in to some
sort of order.

I've created the code below to check for certain text within cells and if
present, delete the containing row. The code works well enough, but as i
have 60,000 rows it is taking a VERY LONG time. is there any way to
significantly speed up this task?

'=========
Sub rowdel()

Dim Tcell As Range, x, y
x = 0
y = 0

For Each Tcell In Selection

If InStr(1, Tcell.Value, "<DIR .") 0 Then
Tcell.Rows.Delete
x = x + 1
End If

y = y + 1
Application.StatusBar = y

Next Tcell

MsgBox x & " lines removed"
Application.StatusBar = False

End Sub

'=========

Thanks for any help,

Tim

speeding up this code
 

Try this
Dim Tcell As Range, x, y
x = 0
y = 0
Application.screenupdating = false
For Each Tcell In Selection

If InStr(1, Tcell.Value, "<DIR .") 0 Then
Tcell.Rows.Delete
x = x + 1
End If

y = y + 1
Application.StatusBar = y

Next Tcell

MsgBox x & " lines removed"
Application.StatusBar = False
Application.screenupdating = true
End Sub

"Tim" wrote:

Hi All,

I've got a directory listing dumped into an Excel sheet (created from the
command line with 'dir \s dir.txt'), which i am trying to get in to some
sort of order.

I've created the code below to check for certain text within cells and if
present, delete the containing row. The code works well enough, but as i
have 60,000 rows it is taking a VERY LONG time. is there any way to
significantly speed up this task?

'=========
Sub rowdel()

Dim Tcell As Range, x, y
x = 0
y = 0

For Each Tcell In Selection

If InStr(1, Tcell.Value, "<DIR .") 0 Then
Tcell.Rows.Delete
x = x + 1
End If

y = y + 1
Application.StatusBar = y

Next Tcell

MsgBox x & " lines removed"
Application.StatusBar = False

End Sub

'=========

Thanks for any help,

Tim

speeding up this code
 

Sub rowdel()
Dim Tcell As Range, x, y
Dim startCol As Long
Dim rng As Range

x = 0: y = 0


Set rng = Range(Cells(1, Selection.Cells(1, 1).Column),
Selection.End(xlDown))
startCol = rng.Column
Columns(startCol + 1).Insert
Cells(1, startCol + 1).Value = "temp"
With Cells(2, startCol + 1)
.FormulaR1C1 = "=AND(RC[-1]<"""",ISNUMBER(FIND(RC[-1],""<DIR
.."")))"
.AutoFill .Resize(rng.Rows.Count - 1)
End With

rng.Resize(, 2).AutoFilter field:=2, Criteria1:="=TRUE"

Set rng = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Columns(startCol + 1).Delete
Application.StatusBar = False

End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim" <tmarsh-trousers-@-take off my trousers to reply-blueyonder.co.uk
wrote in message ...
Hi All,

I've got a directory listing dumped into an Excel sheet (created from the
command line with 'dir \s dir.txt'), which i am trying to get in to some
sort of order.

I've created the code below to check for certain text within cells and if
present, delete the containing row. The code works well enough, but as i
have 60,000 rows it is taking a VERY LONG time. is there any way to
significantly speed up this task?

'=========
Sub rowdel()

Dim Tcell As Range, x, y
x = 0
y = 0

For Each Tcell In Selection

If InStr(1, Tcell.Value, "<DIR .") 0 Then
Tcell.Rows.Delete
x = x + 1
End If

y = y + 1
Application.StatusBar = y

Next Tcell

MsgBox x & " lines removed"
Application.StatusBar = False

End Sub

'=========

Thanks for any help,

Tim