Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help speeding up my code | Excel Programming | |||
speeding up copy and paste code | Excel Programming | |||
Help on cleaning / speeding up code | Excel Programming | |||
Speeding Up Code | Excel Programming | |||
Online Resources for Speeding Up Code | Excel Programming |