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

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