Hi there,
I'm trying to write a macro for a spreadsheet that will have a variable
number of rows, but generally in the neighborhood of 18,000 to 19,000. Here
is what I have, but it takes about 5 minutes to run so I'm trying to find a
faster way:

Sub Format098sheets()

' remove all data other than 098 category sales

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

ElseIf .Cells(Lrow, "d").Value < "98" Then .Rows(Lrow).Delete
'This will delete each row without the Value "98" in Column
d, case sensitive.

End If
Next
End With


My thought is to first sort the data (ascending order) by the column in
question, which I already have code for, then have a macro find the first
cell that has the value '99' and delete it and ALL rows to the end of the
worksheet, then resort the sheet, find the first cell in the column with '97'
and delete it and all rows to the end of the worksheet, which will leave only
the cells with '98' in column D. Can anyone come up with a bit of simple
code for this?

Again, the sorting part I have no problem with, but the find, select rows,
and delete part is giving me fits.
--
Marty

Doing 1 big delete is a lot faster than doing thousands of little deletes.
Give this code a try...

Sub Format098sheets()

' remove all data other than 098 category sales
Dim rng As Range
Dim rngToDelete As Range
Dim rngToSearch As Range

With ActiveSheet
Set rngToSearch = .Range(.Range("D2"), .Cells(Rows.Count, "D").End(xlUp))
.DisplayPageBreaks = False
For Each rng In rngToSearch
If rng.Value < "98" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub
--
HTH...

Jim Thomlinson


"Xman019" wrote:

Hi there,
I'm trying to write a macro for a spreadsheet that will have a variable
number of rows, but generally in the neighborhood of 18,000 to 19,000. Here
is what I have, but it takes about 5 minutes to run so I'm trying to find a
faster way:

Sub Format098sheets()

' remove all data other than 098 category sales

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

ElseIf .Cells(Lrow, "d").Value < "98" Then .Rows(Lrow).Delete
'This will delete each row without the Value "98" in Column
d, case sensitive.

End If
Next
End With


My thought is to first sort the data (ascending order) by the column in
question, which I already have code for, then have a macro find the first
cell that has the value '99' and delete it and ALL rows to the end of the
worksheet, then resort the sheet, find the first cell in the column with '97'
and delete it and all rows to the end of the worksheet, which will leave only
the cells with '98' in column D. Can anyone come up with a bit of simple
code for this?

Again, the sorting part I have no problem with, but the find, select rows,
and delete part is giving me fits.
--
Marty

I experimented with not including and including
Application.ScreenUpdating = False
before starting the deleting. The 2nd went about
twice as fast.

Hth,
Merjet

Jim, This is exactly what I was looking for. Thank you so much! merjet,
thank you too for the suggested improvement for my previous code. I'll keep
that in mind for the future!
--
Marty


"Jim Thomlinson" wrote:

Doing 1 big delete is a lot faster than doing thousands of little deletes.
Give this code a try...

Sub Format098sheets()

' remove all data other than 098 category sales
Dim rng As Range
Dim rngToDelete As Range
Dim rngToSearch As Range

With ActiveSheet
Set rngToSearch = .Range(.Range("D2"), .Cells(Rows.Count, "D").End(xlUp))
.DisplayPageBreaks = False
For Each rng In rngToSearch
If rng.Value < "98" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub
--
HTH...

Jim Thomlinson


"Xman019" wrote:

Hi there,
I'm trying to write a macro for a spreadsheet that will have a variable
number of rows, but generally in the neighborhood of 18,000 to 19,000. Here
is what I have, but it takes about 5 minutes to run so I'm trying to find a
faster way:

Sub Format098sheets()

' remove all data other than 098 category sales

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

ElseIf .Cells(Lrow, "d").Value < "98" Then .Rows(Lrow).Delete
'This will delete each row without the Value "98" in Column
d, case sensitive.

End If
Next
End With


My thought is to first sort the data (ascending order) by the column in
question, which I already have code for, then have a macro find the first
cell that has the value '99' and delete it and ALL rows to the end of the
worksheet, then resort the sheet, find the first cell in the column with '97'
and delete it and all rows to the end of the worksheet, which will leave only
the cells with '98' in column D. Can anyone come up with a bit of simple
code for this?

Again, the sorting part I have no problem with, but the find, select rows,
and delete part is giving me fits.
--
Marty