Centering a picture in a merged cell using VBA

Hello,

I am new to using VBA, but thanks to forums like these I have had success in achieving most of what I set out to accomplish with this specific workbook.

One thing I am hung up on is finding a solution to properly centering a picture in an area of merged cells once it has been inserted via the same macro.

Here's a screenshot of what I am attempting to achieve:

Screenshot_20230223_090747.png

Here's a screenshot of what happens when I run the macro below:

Screenshot 2023-02-23 212231.jpg

As you can see, I have a merged cell with the range A12:AZ31. I have attempted about eight different solutions that apparently worked for others on various forums, however there must be something unique about my case as none of them seem to quite work for me. Here's what I've got as an example, but again this does not center the image properly either:

Sub AddOverviewPhoto()

' AddOverviewPhoto Macro
' Adds and resizes the main report photo.

ActiveSheet.Unprotect

Dim photoNameAndPath As Variant
Dim photo As Picture
Dim r As Range

photoNameAndPath = Application.GetOpenFilename(Title:="Select Photo to Insert")
If photoNameAndPath = False Then Exit Sub
Set r = ActiveSheet.Range("A12:AZ31")
Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath)
With photo
.Left = r.Left + (r.Width - .Width) / 2
.Top = r.Top + (r.Height - .Height) / 2
.Height = r.Height * 0.98
End With

ActiveSheet.Protect

End Sub

I've read about and tried to incorporate .MergeArea where some forum posts had suggested, but I haven't seemed to get anywhere with that either.

The picture insertion and resizing work perfectly fine, just need to get the darn thing centered in that range of merged cells.

Any and all suggestions are greatly appreciated!

Thanks,

Matt