[SOLVED] Remove 'The ' from the beginning of a book title

Remove 'The ' from the beginning of a book title

I have a column of book titles and I would like to run a VBA script that would remove the word "The " from the beginning of the title and leave any other instances of "The " alone. Ex: Beyond The Breach.

I found this code:

Sub MyReplaceMacro()

Dim lastRow As Long
Dim myRange As Range
Dim remove1 As String

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set myRange = Range("A2:A" & lastRow)
myRange.Replace What:="The ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
        
End Sub

But it removes any and all instances of "The " in the titles.

I know you can use LEFT also but I don't know how I tell it that I only want the first four digits removed if they are "The ". Hope I explained that well enough. I can nearly think of what I want in words but do not know the syntax.

Solution

Sub RemoveLeadingTheAndCapitalize()

    Dim lastRow As Long
    Dim myRange As Range
    Dim cell As Range
    Dim title As String

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set myRange = Range("A2:A" & lastRow)
    
    For Each cell In myRange
        title = cell.Value
        ' Check if the title starts with "The "
        If Left(title, 4) = "The " Then
            ' Remove "The " and capitalize the first letter of the new title
            title = Mid(title, 5)
            cell.Value = UCase(Left(title, 1)) & Mid(title, 2)
        End If
    Next cell

End Sub