Macro to Convert Value to Number, Sort, then Delete
hi Dan,
I changed my test werkbook to 10 sheets and added a loop to my code
I used a bit from Rick sample for the loop.
Sub cardan()
'
' cardan Macro
'
Const WSnames As String = _
"Sheet1,Sheet2,Sheet3,Sheet4,Sheet5," & _
"Sheet6,Sheet7,Sheet8,Sheet9,Sheet10"
Dim lngStart As Long
Dim lngEnd As Long
Dim WS As Worksheet
lngStart = timeGetTime
'
For Each WS In Worksheets(Split(WSnames, ","))
WS.Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("A6").Select
ActiveSheet.Range("$A$1:$CC$8001").AutoFilter _
Field:=1, Criteria1:="Delete"
While ActiveCell.Text < "Delete"
ActiveCell.Offset(1, 0).Select
Wend
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A5").Select
Next
lngEnd = timeGetTime
MsgBox lngEnd - lngStart & " milliseconds"
End Sub
At my computer it needed 3 minutes and 16 seconds to complete.
I placed the ScreenUpdating inside the loop so you can see the
selection of each sheet.
HTH,
Wouter
|