Spaces:
Sleeping
Sleeping
File size: 2,117 Bytes
38fdd3d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | 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
|