Shrink cells selected by 1 on each side

user_11424791 0
Viewed 47

When a user selects a certain range of cells, for example A1:A10, I want the macro to shrink the selection down to A2:A9. I've only managed to get the addresses of the selected cells so far.

I'm quite new to VBA so an example would be quite appreciated Thanks!

Sub macro1()
    Dim addr As String
    addr = Selection.Address
    MsgBox addr

End Sub

enter image description here

enter image description here

vba excel
1 Answers

This will shrink your selection by 1 column on each side (only works if there are at least 3 columns select):

Sub ShrinkSelection()
    With Selection
        If .Columns.Count > 2 Then
            .Cells.Offset(0, 1).Resize(, .Columns.Count - 2).Select
        Else
            MsgBox "Selection must have at least 3 columns."
        End If
    End With
End Sub
user_155979360
Related