I have a VBA macro for a file of GPS data. It copies rows and subsequent rows to a new worksheet when the GPS data shows movement to the south.
It works fine, but is pretty slow on larger files (20,000 rows+). I'm looking for suggestions on how to speed it up.
The cell values in the macro are from another macro analyzing the GPS data. 1 means movement North, 2 means no movement, 3 means movement South. I could easily change these to different datatype if that would speed up this macro.
Sub SplitIntoGroups() Application.ScreenUpdating = False 'The data is incomplete in the first 50 and last 50 rows, so this is so it doesn't analyze that data Dim NearEnd As Integer Dim NearStart As Integer NearStart = 52 NearEnd = (Cells(Sheet1.Rows.Count, 3).End(xlUp).row) - 50 'Groups are the different sheets of data. I start with zero, because the first is usually unimportant Dim Groups As Integer Dim r As Integer 'GroupsCheck is the variable used to go through each row Dim GroupsCheck As Integer 'WentNorth is a boolean to ensure there was movement to the north before creating a new sheet. Dim WentNorth As Boolean Sheets.Add.Name = "Group 0" Groups = 0 'r is used as the row offset, so each row can be copied on the next row in the proper worksheet r = 0 For GroupsCheck = NearStart To NearEnd If Sheets("Sheet1").Cells(GroupsCheck, "AB").Value = 1 Then WentNorth = True End if r = r + 1 'once north movement (WentNorth=true) exists, a new sheet will be created when south movement first occurs (cells = 3 and previous cell <3) If Sheets("Sheet1").Cells(GroupsCheck, "AB").Value = 3 And Sheets("Sheet1").Cells(GroupsCheck - 1, "AB").Value < 3 And WentNorth = True Then Groups = Groups + 1 Sheets.Add.Name = "Group " & Groups 'once a new sheet is created, r and WentNorth are reset r = 0 WentNorth = False End If 'the copying function Worksheets("Sheet1").Range("A" & GroupsCheck & ": AB" & GroupsCheck).Copy Worksheets("Group " & Groups).Range("A2:AE2").Offset(r, 0) Next GroupsCheck Application.ScreenUpdating = True End Sub