Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows, takes a long time!
Hi, i received pieces of this code below to delete all the
rows in a spreadsheet where in a certain column the letter d is present and in another column that row is blank, the code below works great, when its ran in a group of other modules, but if i run this particular mod all by its self it takes it self a long time to run. does anyone have a clue why? or how i can speed it up as if it was run from a group of modules. screen updating is turned off, along with calculation when this macro runs. see code below: Application.ScreenUpdating = False Sheets("report").Select Sheets("losstnspreadsheet").Select ActiveSheet.Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select myrow = Selection.Row - 1 Dim findstring As String findstring = "D" Set b = Range("G2:G" & myrow).Find(What:=findstring _ & "*", LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Range("G2:G" & myrow).Find(What:=findstring _ & "*", LookAt:=xlWhole) Wend On Error Resume Next 'In case there are no blank rows ActiveSheet.Range("G2:G" & myrow).SpecialCells (xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 ActiveSheet.Range("A1").Select Application.ScreenUpdating = True |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows, takes a long time!
Try putting something like this near the top.
Sheets("report").DisplayPageBreaks = False If you can see those dotted lines, then excel wants to determine where they should be put--each time you delete a row. If you turn it off, excel won't try to determine this. Nick wrote: Hi, i received pieces of this code below to delete all the rows in a spreadsheet where in a certain column the letter d is present and in another column that row is blank, the code below works great, when its ran in a group of other modules, but if i run this particular mod all by its self it takes it self a long time to run. does anyone have a clue why? or how i can speed it up as if it was run from a group of modules. screen updating is turned off, along with calculation when this macro runs. see code below: Application.ScreenUpdating = False Sheets("report").Select Sheets("losstnspreadsheet").Select ActiveSheet.Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select myrow = Selection.Row - 1 Dim findstring As String findstring = "D" Set b = Range("G2:G" & myrow).Find(What:=findstring _ & "*", LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Range("G2:G" & myrow).Find(What:=findstring _ & "*", LookAt:=xlWhole) Wend On Error Resume Next 'In case there are no blank rows ActiveSheet.Range("G2:G" & myrow).SpecialCells (xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 ActiveSheet.Range("A1").Select Application.ScreenUpdating = True -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting takes too long | Excel Worksheet Functions | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
Loading MS Query takes a long time | Excel Worksheet Functions | |||
Excel Help takes a very long time to load | Excel Discussion (Misc queries) | |||
Save takes long time | Excel Discussion (Misc queries) |