Skip to main content

How to organize images automatically in an Excel file using a macro?

If you add multiple images at once to an Excel file, Excel will try to squeeze them in once cell. It will probably look like the screenshot below.

Instead of manually resizing and relocating these images, you can use the macro below to automatically organize all images in milliseconds.

Sub organizeImages()
     lastTop = 0
     staticHeight = 500

     For Each pic In Worksheets("Sheet1").Pictures
          pic.Top = lastTop
          pic.Height = staticHeight
          lastTop = lastTop + pic.Height + 15
          pic.Left = 0
     Next pic
End Sub

Images resized and relocated automatically:

How to export all comments from an Excel file to Word

Here is the VBA macro which export the comments in the active sheet to a newly created Word file:

Sub extractComments()
    Dim xComment As Comment
    Dim wApp As Object
    On Error Resume Next
Set wApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
        Err.Clear()
        Set wApp = CreateObject("Word.Application")
End If

    wApp.Visible = True
    wApp.Documents.Add DocumentType:=0

For Each xComment In Application.ActiveSheet.Comments
        wApp.Selection.TypeText xComment.Parent.Value & vbTab & xComment.Parent.Address & vbTab & xComment.Text
wApp.Selection.TypeParagraph
    Next

Set wApp = Nothing
End Sub

Source. Added cell value into the code above