Fit size of a picture to fill entire range

Question

That's my code:

With Worksheets("CheckListIndustrialisation").Pictures.Insert(image) 
    '.Top = [F31].Top
    '.Left = [F31].Left
    '.Width = [F31].Width
    '.Height = [F31].Height

I want to show many photos in one sheet, in different cell of big size.

The problem is that my image stay very small in the big cell. How to dimension it so fills the entire cell or range? For example, given the range A1:D4, the image should occupy the entire space within that range.

Answer 1

Two things:

1. You need to specify the top/left/height/width of the entire range, not just e.g. cell F31 as in your example.

2. You may want to unlock the aspect ratio. This will allow the image to stretch both vertically and horizontally to fit your range.

This is what this code does:

Dim r As Range
Dim ws As Worksheet
Dim imagePath As String
Dim img As Picture

Set ws = Worksheets("CheckListIndustrialisation")
Set r = ws.Range("A1:D4")
imagePath = "C:\myImage.jpg"
Set img = ws.Pictures.Insert(imagePath)

With img
    .ShapeRange.LockAspectRatio = msoFalse
    .Top = r.Top
    .Left = r.Left
    .Width = r.Width
    .Height = r.Height
End With

Result: