VBA Code for a template

[I/ATech]

Learning VBA. We race endurance and use a datalogger that logs RPM every tenth of a second, which I download and convert to .xlsx. Each driver runs approximately 2 hours, so there are around 72,000 data lines per driver. Using the macro recorder, I cobbled together code that adds a column of sequence, 1 to however many, and then I want to sort the sheet by RPM, largest to smallest. I don’t think I have the code in a form to be able to create a template (.xltm), so it can be used multiple times. (I created this on a sheet named John1 (3)). Thank you advance for any help.

John	Time	Time (s)	Distance [feet]	MX5 RPM [rpm]
Lap: 1	01.43.635	0​	0​	0​
		0.1​	0​	0​
		0.2​	0​	0​
		0.3​	0​	0​
		0.4​	0​	0​
		0.5​	0​	0​
		0.6​	0​	0​
		0.7​	0​	0​
		0.8​	0​	0​
		0.9​	0​	0​
		1​	0​	0​
		1.1​	0​	160.338​
		1.2​	0​	801.926​
		1.3​	0​	803.336​
		1.4​	0​	1055.296​
		1.5​	0​	1300.443​
		1.6​	0.01​	1425.19​
		1.7​	0.01​	1545.473​

Sub AddSeqAndSortMaxRPM()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Seq"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").AutoFill Range("A2:A" & Range("E" & Rows.Count).End(xlUp).Row), xlFillSeries
ActiveWorkbook.Worksheets("John1 (3)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("John1 (3)").Sort.SortFields.Add2 Key:=Range( _
"F2:F32398"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("John1 (3)").Sort
.SetRange Range("A1:F32398")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[rlv01]

John	Time	Time (s)	Distance [feet]	MX5 RPM [rpm]
Lap: 1	01.43.635	0	0	0
		0.1	0	0
		0.2	0	0
		0.3	0	0
		0.4	0	0
		0.5	0	0
		0.6	0	0
		0.7	0	0
		0.8	0	0
		0.9	0	0
		1	0	0
		1.1	0	160.338
		1.2	0	801.926
		1.3	0	803.336
		1.4	0	1055.296
		1.5	0	1300.443
		1.6	0.01	1425.19
		1.7	0.01	1545.473

Perhaps something like this?

Sub AddSeqAndSortMaxRPM()
    Dim RpmSortSheet As Worksheet
    Dim SortRange As Range
    
    Set RpmSortSheet = ActiveSheet
    
    Select Case MsgBox("This action will sort worksheet '" & RpmSortSheet.Name & "' by rpm. " & vbCrLf _
            & "" & vbCrLf _
            & "Continue?", vbOKCancel Or vbQuestion, Application.Name)
        Case vbCancel
            Exit Sub
    End Select
    
    With RpmSortSheet
        If .Range("A1") <> "Seq" Then
            .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Set SortRange = .Range("A1:F" & .Range("F" & .Rows.Count).End(xlUp).Row)
            With SortRange
                .Range("A1").Value = "Seq"
                .Range("A2").Value = "1"
                .Range("A2").AutoFill .Offset(1).Resize(.Rows.Count - 1, 1), xlFillSeries
                .Sort Key1:=.Columns("F"), Order1:=xlDescending, Header:=xlYes
            End With
        Else
            Select Case MsgBox("This worksheet has already been sorted by rpm " & vbCrLf _
                    & "" & vbCrLf _
                    & "Revert to original order?", vbYesNo Or vbQuestion, Application.Name)
                Case vbYes
                    Set SortRange = .Range("A1:F" & .Range("F" & .Rows.Count).End(xlUp).Row)
                    SortRange.Sort Key1:=.Columns("A"), Order1:=xlAscending, Header:=xlYes
                    RpmSortSheet.Range("A1").EntireColumn.Delete
            End Select
        End If
    End With
End Sub

which I download and convert to .xlsx

That seems like the bigger task and one that would be more beneficial to automate. Your sort code could be tacked on after.

 

[I/ATech]

Amazing! Thank you so much!