WriteBufferSheet.cls

Attribute VB_Name = "Sheet3"
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet: Set ws = Me
    Dim rng As Range, cell As Range
    Dim exts As Variant
    Dim pasteRow As Long, pasteCol As Long
    Dim buffer As Collection
    Dim i As Long

    OnDataAreaChanged ws, Target

    ' 1) intercept only changes in B2:B8
    Set rng = Intersect(Target, ws.Range("B2:B" & ws.Rows.Count))
    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False
    On Error GoTo Cleanup

    ' 2) valid image extensions
    exts = Split( _
      "apng,avif,bmp,bpg,cgm,cr2,dib,dng,eps,eps2,eps3,epsf,epsi,exr,flif," & _
      "gif,heic,heif,jfi,jfif,jif,jpe,jpeg,jpg,mos,nef,pdf,png,raw,svg,tif," & _
      "tiff,vml,webp,xar", ",")

    ' 3) find top-most row & its column of the pasted block
    pasteRow = ws.Rows.Count
    pasteCol = rng.Column
    For Each cell In rng.Cells
        If cell.Row < pasteRow Then pasteRow = cell.Row
    Next

    ' 4) collect only image URLs, then clear originals
    Set buffer = New Collection
    For Each cell In rng.Cells
        Dim txt As String: txt = Trim(CStr(cell.Value))
        If txt <> "" Then
            If IsImageURL(txt, exts) Then buffer.Add txt
        End If
        cell.ClearContents
    Next

    ' 5) write them out horizontally from the paste-start
    For i = 1 To buffer.Count
        With ws.Cells(pasteRow, pasteCol + (i - 1))
            .Value = buffer(i)
            ws.Hyperlinks.Add Anchor:=.Range("A1"), _
                              Address:=buffer(i), TextToDisplay:=buffer(i)
        End With
    Next

Cleanup:
    Application.EnableEvents = True
End Sub

Private Function IsImageURL(ByVal url As String, exts As Variant) As Boolean
    Dim base As String, e As Variant
    If InStr(url, "?") > 0 Then
        base = Left$(url, InStr(url, "?") - 1)
    Else
        base = url
    End If
    base = LCase(base)
    For Each e In exts
        If Right$(base, Len(e) + 1) = "." & LCase(e) Then
            IsImageURL = True: Exit Function
        End If
    Next
    IsImageURL = False
End Function