Cropping Graphics in a Macro

Written by Allen Wyatt (last updated May 20, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


John often pastes screen shots into an Excel workbook. He invariably needs to crop these graphics on all four sides by a set amount. Because he does this task so often, he wonders if there is a way to do it easily in a macro.

There are a number of ways you can address this problem, and all of them involve the use of macros. (There are no built-in commands to do such a task.) If you simply want to crop a graphic you've already added, you can do so by using a simple macro such as this:

Sub CropPicture()
    With Selection.ShapeRange.PictureFormat
        .CropLeft = 200
        .CropTop = 300
        .CropBottom = 50
        .CropRight = 100
    End With
End Sub

You would, of course, need to specify the values for the cropping. These values are specified in points, and they are relative to the original size of the picture. So, for instance, if you previously scaled the selected picture to 150% of its original size, and then you run this macro, all of the crop settings will appear to be 1.5 times their actual values. (So, for instance, the left cropping would appear to be 300 pixels, not 200 pixels.)

The macro could easily be modified so that it does the pasting and then automatically crops the image. This is done by simply adding a single-line paste command:

Sub PasteCropPicture()
    ActiveSheet.Paste
    With Selection.ShapeRange.PictureFormat
        .CropLeft = 200
        .CropTop = 300
        .CropBottom = 50
        .CropRight = 100
    End With
End Sub

The macro assumes that you previously copied the screen shot to the Clipboard; if you try to run it without having done so, you may well get an error.

There is one other thing to remember when running a macro such as this: When it is done, it will appear that your image has moved from its original position in the worksheet. This occurs because cropping from all four sides will change the apparent upper-left corner of the image to a different location. You can, if desired, compensate for this in your macro by adding code that "remembers" the upper-left corner of the image:

Sub CropPositionPicture()
    Dim MyShape As ShapeRange
    Dim LeftSide As Single
    Dim TopSide As Single

    Set MyShape = Selection.ShapeRange
    LeftSide = MyShape.Left
    TopSide = MyShape.Top

    With MyShape.PictureFormat
        .CropLeft = 200
        .CropTop = 300
        .CropBottom = 50
        .CropRight = 100
    End With

    MyShape.Left = LeftSide
    MyShape.Top = TopSide
End Sub

Of course, there is one thing to remember when you choose to crop graphics, regardless of how you choose to do so. Cropping doesn't affect image size. So, for instance, if you crop a screen shot so that only half of the screen is visible, all the "invisible" portions of the screen shot are still there, taking up space. This adds to the overall size of your workbook, sometimes amazingly so.

The only way around this is to not crop any graphics within Excel. Instead, process the image in a graphics program, save your processed image, and then insert that processed image in the worksheet. The result is smaller workbooks which means faster saving and loading.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11580) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hiding Graphics

Do you need a printout where graphics can be turned on and off? This tip provides some concrete ways you can get just ...

Discover More

Putting Style Names Next to Paragraphs on a Printout

If you use styles to format your document, you might want a way to print the document and show, to the left of each ...

Discover More

Spacing Before and After Tables

Tables can be a necessity in many types of documents. However, they can be a bother to get positioned properly relative ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Easily Changing the Default Drive and Directory

Need a quick way to change the default drive and directory in a macro you are writing? Here's the commands to do it and a ...

Discover More

Calling a Subroutine from a UDF

Excel allows you to create a special type of macro called a user-defined function (UDF). These can let you add to the ...

Discover More

Adjusting Values with Formulas

Paste Special is a great tool that allows you to modify the values in a range of cells in your worksheets. You may want, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 1?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.