[엑셀] 여러 이미지 세로 간격을 동일하게 조정하는 스크립트

Alt + F11

삽입-모듈

스크립트 입력

실행

Sub 이미지_세로정렬_간격5()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim pictureShapes() As Shape
    Dim count As Integer
    Dim i As Integer, j As Integer
    Dim temp As Shape
    Dim topPos As Single
    Dim spacing As Single

    Set ws = ActiveSheet
    spacing = 5
    count = 0

    ' 이미지 개수 세고 배열에 저장
    For Each shp In ws.Shapes
        If shp.Type = msoPicture Then
            count = count + 1
            ReDim Preserve pictureShapes(1 To count)
            Set pictureShapes(count) = shp
        End If
    Next shp

    ' Y 좌표 기준으로 버블 정렬
    For i = 1 To count - 1
        For j = i + 1 To count
            If pictureShapes(i).Top > pictureShapes(j).Top Then
                Set temp = pictureShapes(i)
                Set pictureShapes(i) = pictureShapes(j)
                Set pictureShapes(j) = temp
            End If
        Next j
    Next i

    ' 정렬 및 위치 재배치
    topPos = 10
    For i = 1 To count
        With pictureShapes(i)
            .Top = topPos
            .Left = Range("A1").Left
            topPos = topPos + .Height + spacing
        End With
    Next i
End Sub