Can someone help me to revise my code to make it run faster in a large amount of data...
My data usually handles 50K records. The macro seems not working because it took me an hour when it reaches this code:
dDate = currmth & " 1, " & curryr
Lrow = LastRow(sheet_logs)
With .Range(.Cells(2, 1), .Cells(Lrow, 5))
.AutoFilter
.AutoFilter Field:=3, Criteria1:="<" & Format(dDate, "mm/dd/yyyy")
End With
.Range(.Cells(3, "A"), .Cells(Lrow, "C")).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range(.Cells(3, "A"), .Cells(Lrow, "C")).AutoFilter Field:=3
.Range("C2").Select
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add Key:= _
Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add Key:= _
Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Bookmarks