G
Guest
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.
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.