Excel VBA‎ > ‎

Range Object in VBA

'Remove most formatting, contents, borders and autofit the cells contents. Great to use instead of clearcontents since it does a much cleaner job
Sub cleanrange(myrange As Range)
    myrange.ClearContents
    myrange.UnMerge
    myrange.Font.Bold = False
    myrange.Font.Italic = False

    myrange.EntireRow.AutoFit
    With myrange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With myrange
        On Error Resume Next
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
        On Error GoTo 0
    End With
End Sub