In response to your email request to also format the K34
value, I suggest the below appended code.

Note that it's possible to dispence with the named range
"rng_Letters" and use another array instead. The macro
could extract the letters from the $H$50 cells in the
necessary worksheets and populate the array accordingly.
Then the elements in this array could be compared to the
elements in the existing Arr array instead of doing the
comparison to the cells in the named range.

I rejected this (at least for now) because, IMO, for a
workbook this large, it's usually a good idea to have a
helper sheet. However, I strongly advise hiding the sheet
(xlVeryHidden). You can use it to store values, notes,
settings or whatever. For this particular workbook, there
is a complication involved in iterating through the
worksheets in order to populate the array that I won't go
into for the sake of brevity. Not that it can't be done,
just that I think I would do it this way if it were me.
Maybe I'll change my mind(?). Code follows:


Sub findfirstandlast()
Dim Arr(31) As String
Dim rng As Range, cell As Range
Dim i As Integer
Dim txt1 As String, txt2 As String, txt3 As String
Dim settext As String

Set rng = Range("rng_Letters")
settext = " through to "
txt1 = ""
txt2 = ""
txt3 = ""

On Error Resume Next
For i = 0 To 25
Arr(i) = Chr(i + 65)
Next
For i = 26 To 30
Arr(i) = "A" & Chr(i + 39)
Next

For i = 0 To 30
For Each cell In rng
If Trim(cell) = Arr(i) Then
txt1 = Arr(i)
Exit For
End If
Next
If txt1 < "" Then Exit For
Next

For i = 30 To 0 Step -1
For Each cell In rng
If Trim(cell) = Arr(i) Then
txt2 = Arr(i)
txt3 = Arr(i + 1)
Exit For
End If
Next
If txt2 < "" Then Exit For
Next

Sheets("Data Entry_").Select
Range("K33") = txt1 & settext & txt2
Range("K34") = txt3
On Error GoTo 0
End Sub

Regards,
Greg