Select a Column without using .Select

Question

I have created a VBA macro in excel. It works; however, every forum I read states I should avoid using Select. As I am a newbie, I do not know how to implement it nor how it would work.

The code does the following:

1. Copy contents in Column B to Column E, then delete Column B
   • Column E then becomes Column D
2. Format all cells in Column D to wrap text
3. Use Data text to Column feature on Column D
   • Delimiter based on line breaks. (Other: CTRL J)

Sub TestRun()
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 25.13

    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), _
        TrailingMinusNumbers:=True
End Sub

Answer 1

Simply just replace .Select with the Method (action) or Property (attribute) that you will be using. Then delete all unecessary actions that Select would normally do. For example:

Sub TestRun()
    Columns("B:B").Copy Destination:=Columns("E:E")
    Columns("B:B").Delete Shift:=xlToLeft

    With Columns("D:D")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .ColumnWidth = 25.13
        .TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), _
        TrailingMinusNumbers:=True

    End With

End Sub

Answer 2

Here is a simple example using the With statement with out using variables to move col2 to col4.

'Identify your workbook and sheet, change the sheet name as needed
With ThisWorkbook.Sheets("Sheet1")

    'First - Cut col2 
    .Columns(2).EntireColumn.Cut

    'Second - Insert col2 at col5 shifting the current col5 to the right. 
    .Columns(5).EntireColumn.Insert Shift:=xlRight

    'col3 etc. will then shift left because col2 was cut and moved to col5
End With

You can use With Columns(4) inside the above With statement to format the column, try to write the code and if you have any more questions then please ask.