Crop Selection Window Excel VBA Macro

This macro example crops the Excel active window to fit the selected range. It does the trick with some useful properties of the Application and ActiveWindow VBA objects.

Macro/VBA code:

 

  Sub CropSelectionWindow()

      HideDisplay

      Dim rHeight As Long, cWidth As Long

      rHeight = Selection.Height

      cWidth = Selection.Width

      With ActiveWindow

          .WindowState = xlNormal

          .Height = rHeight + 44

          .Width = cWidth + 1

      End With

      ActiveSheet.Range("A1").Select

  End Sub

 

  Sub HideDisplay()

      Application.DisplayFullScreen = True

      ActiveWindow.DisplayVerticalScrollBar = False

      ActiveWindow.DisplayHorizontalScrollBar = False

      ActiveWindow.DisplayHeadings = False

  End Sub

 

Macro explained:

• At the start we call the HideDisplay procedure, where we set the full screen display and hide scroll bars and headings.
• We declare and define two variables (rHeight, cWidth) to hold the height and width of the selected area. This is the value in pixels within the active window area.
• Then we set the size of the active window with the Height and Width properties of the ActiveWindow object. Note we add 44 pixels to the height to account for top and bottom bars and borders, and just 1 pixel to the width to account for the borders of the window.
• Finally, we select a cell in the range to ensure showing the selection fitting the active window.

This is how we crop the selection window in Excel with VBA macros.

Other examples:

• How to create a Simple Digital Clock Timer with Excel VBA macros 
  
• How to Copy Selection To Other Workbook with Excel VBA macros 
  
• How to Add Shapes To Selection with Excel VBA macros