speeding up this code

[Tim]

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

 

[Tim]

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

 

[Guest]

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

 

[Bob Phillips]

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)