ShrinkToFit Contents Excel Range VBA (Visual Basic for Applications)

ShrinkToFit Contents Excel Range VBA (Visual Basic for Applications)

ShrinkToFit

Fits text into available column width where it may penalize the font size to fit the contents. ShrinkToFit property can be applied on various objects like Range and its underlying objects like Rows or Columns to apply shrink to fit. Its boolean property which can be sets to True or False.

Range

Public Sub ShrinkTofit()
    
    'Declare range object
    Dim oRange As Range
    
    'Bind selection
    Set oRange = Selection
    
    'fit entire selected range
    oRange.ShrinkTofit = True
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Rows

Public Sub ShrinkTofitRows()
    
    'Declare range object
    Dim oRows As Range
    
    'Bind selection
    Set oRows = ThisWorkbook.Worksheets("Sheet4").Rows
    
    'fit entire selected range
    oRows(2).ShrinkTofit = True
    
    'memory cleanup
    Set oRows = Nothing
End Sub

Columns

Public Sub ShrinkTofitColumns()
    
    'Declare range object
    Dim oColumns As Range
    
    'Bind selection
    Set oColumns = ThisWorkbook.Worksheets("Sheet4").Columns
    
    'fit entire selected range
    oColumns(1).ShrinkTofit = True
    
    'memory cleanup
    Set oColumns = Nothing
End Sub

Subscribe to our blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.