folks,

good day can any one help me cut this down into more elgance looking its getting a bit long now i am getting confused with the long code

it basically searches the column and numbers the placeholder in each column -I am just repeating for each column

 Sub Number_Items()
    
    Dim lngIndex As Long: lngIndex = 1
    Dim lngIndex1 As Long: lngIndex = 1
    
    Dim oLastRow As Long, i As Long
    
   
    '------ Column D  : Category1
    oLastRow = Range("D" & Rows.Count).End(xlUp).Row
    
    For i = 9 To oLastRow
    
    With Range("D" & i)
    .Value = Replace(.Value, "DXX", lngIndex)     ' << Search and Replace here
    
    lngIndex = lngIndex + 1
    End With
    Next i
    
    
    
    '------ Column E  : Category2
    oLastRow = Range("E" & Rows.Count).End(xlUp).Row
    
    For i = 9 To oLastRow
    
    With Range("E" & i)
    .Value = Replace(.Value, "EXX", lngIndex)     ' << Search and Replace here
    
    lngIndex = lngIndex + 1
    End With
    Next i
    
    '-------- Column F : Name
    oLastRow = Range("F" & Rows.Count).End(xlUp).Row
    
    For i = 9 To oLastRow
    
    With Range("F" & i)
    .Value = Replace(.Value, "FXX", lngIndex1)     ' << Search and Replace here
    
    lngIndex1 = lngIndex1 + 1
    End With
    Next i
    
       '-------- Column G : ID
    oLastRow = Range("G" & Rows.Count).End(xlUp).Row
    
    For i = 9 To oLastRow
    
    With Range("G" & i)
    .Value = Replace(.Value, "GXX", lngIndex1)     ' << Search and Replace here
    
    lngIndex1 = lngIndex1 + 1
    End With
    Next i
    
         '-------- Column H : Notes
    oLastRow = Range("HXX" & Rows.Count).End(xlUp).Row
    
    For i = 9 To oLastRow
    
    With Range("H" & i)
    .Value = Replace(.Value, "&", lngIndex1)     ' << Search and Replace here
    
    lngIndex1 = lngIndex1 + 1
    End With
    Next i
    

    End Sub

may be a select case statement but i couldnt work out where to put the code and the rest of the code for the case range

thank you for any help