I currently have a sorting script that will check line by line for a certain string. If the string matches, then that line will be copied to that worksheet and the line will be deleted. However, sometimes I need to sort tens of thousands of rows and it can take a long time or will crash Excel. Is there a way to rewrite it to be able to sort more efficiently?
In my code, I have the With ws2 block copied multiple times with different sheet names
Dim ws1 As Worksheet, ws2 As Worksheet Dim firstRowWs1 As Long Dim lastRowWs1 As Long Dim lastRowWs2 As Long Dim searchColumnWs1 As Integer Dim i As Integer Dim check As Variant Dim strSearch As Variant Dim foundMatches As Boolean Dim sDate As String sDate = Format(Now(), "mm/dd/yyyy") Set ws1 = Worksheets("Sort") Set ws3 = Worksheets("Overview") Set ws2 = Worksheets("sheet2") With ws2 lastRowWs1 = ws1.UsedRange.Rows.Count lastRowWs2 = ws2.UsedRange.Rows.Count firstRowWs1 = 1 searchColumnWs1 = 10 strSearch = Array("john") For i = firstRowWs1 To lastRowWs1 For Each check In strSearch If check = ws1.Cells(i, searchColumnWs1).Value Then ws1.Rows(i).Copy (ws2.Rows(lastRowWs2 + 1)) ws2.Rows(lastRowWs2 + 1).Columns("A:B").Insert xlToRight lastRowWs2 = lastRowWs2 + 1 ws1.Rows(i).Delete shift:=xlUp i = i - 1 Exit For End If Next check Next i End With