speeding up this code

T

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
 
T

Tim

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

Mike

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top