Write macros to automatically Sort AND Convert Letters to Numbers

taimysho0,

Something like this should accomplish what you're looking for:

Sub tgr()
    
    Dim rngData As Range
    Dim arrData As Variant
    Dim DataIndex As Long
    
    Set rngData = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    arrData = rngData.Value
    
    For DataIndex = 1 To UBound(arrData, 1)
        If Not IsNumeric(Left(arrData(DataIndex, 1), 1)) Then
            Select Case LCase(arrData(DataIndex, 1))
                Case "a": arrData(DataIndex, 1) = 1
                Case "b": arrData(DataIndex, 1) = 2
                Case "c": arrData(DataIndex, 1) = 4
                Case "d": arrData(DataIndex, 1) = 5
                Case "e": arrData(DataIndex, 1) = 7
                Case "f": arrData(DataIndex, 1) = 9
                Case "x", "y", "null": arrData(DataIndex, 1) = String(255, "z")
            End Select
        End If
    Next DataIndex
    
    With rngData
        .Value = arrData
        .NumberFormat = "000"
        .EntireRow.Sort .Cells, xlAscending, Header:=xlNo
        .AutoFilter 1, String(255, "z")
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    
End Sub

taimysho0,

Attached is an example workbook based on the criteria described. It contains the proposed macro in a standard module. I am able to run it with no errors. The filter is to delete all rows that contained "x", "y", or "null". If your data is different or if you continue to get an error, please upload a sample workbook with mock data that is experiencing the problem so that I can troubleshoot the code.

taimysho0,

Updated code. The autofilter is only there to delete rows of cells that contained "x", "y", or "null". I have changed it from a filter to a .Find loop to avoid errors. It ran successfully on your test workbook. Let me know if this works for you:

Sub tgr()
    
    Dim strZ As String
    Dim strFirst As String
    Dim rngData As Range
    Dim rngFound As Range
    Dim rngDel As Range
    Dim arrData As Variant
    Dim DataIndex As Long
    
    strZ = String(255, "z")
    Set rngData = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    arrData = rngData.Value
    
    For DataIndex = 1 To UBound(arrData, 1)
        Select Case LCase(Left(Trim((arrData(DataIndex, 1))), 1))
            Case "a": arrData(DataIndex, 1) = 1 & Mid(arrData(DataIndex, 1), 2)
            Case "b": arrData(DataIndex, 1) = 2 & Mid(arrData(DataIndex, 1), 2)
            Case "c": arrData(DataIndex, 1) = 4 & Mid(arrData(DataIndex, 1), 2)
            Case "d": arrData(DataIndex, 1) = 5 & Mid(arrData(DataIndex, 1), 2)
            Case "e": arrData(DataIndex, 1) = 7 & Mid(arrData(DataIndex, 1), 2)
            Case "f": arrData(DataIndex, 1) = 9 & Mid(arrData(DataIndex, 1), 2)
            Case "x", "y": arrData(DataIndex, 1) = strZ
        End Select
        If LCase(Left(Trim(arrData(DataIndex, 1)), 4)) = "null" Then arrData(DataIndex, 1) = strZ
    Next DataIndex
    
    With rngData
        .Value = arrData
        .NumberFormat = "000000"
        .EntireRow.Sort .Cells, xlAscending, Header:=xlNo
        Set rngFound = .Find(strZ)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Set rngDel = rngFound
            Do While Not rngFound Is Nothing
                Set rngDel = Union(rngDel, rngFound)
                Set rngFound = .Find(strZ, rngFound)
                If rngFound.Address = strFirst Then Exit Do
            Loop
            rngDel.EntireRow.Delete xlShiftUp
        End If
    End With
    
End Sub

To just clear the cell contents instead of deleting the entire row, change this line:

            rngDel.EntireRow.Delete xlShiftUp

To be this instead:

            rngDel.ClearContents

untested, but you could try adding this line to the end of the macro:

Intersect(ActiveSheet.UsedRange, Columns("H")).Formula = "=ROW()-1"