CODEWITHSUNDEEP
excelvba
user17420363
user17420363

Reputation: 25

VBA Cleaning up Columns

I have a macro I wrote that appends some data.

As of now it spits out data populated from Column A to AY (A:AY)

I need most of these columns and their data removed and two columns repeated/copied & pasted.

My goal: (A-G-C-C-D-G-I-AY)

I wrote this code and I have been able to produce (A-C-D-G-I-AY) from it:

Range("B2").Select
Selection.EntireColumn.Delete
Range("D2:E2").Select
Selection.EntireColumn.Delete
Range("E2").Select
Selection.EntireColumn.Delete
Range("F2:AT2").Select
Selection.EntireColumn.Delete
Range("G2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Delete

Note* the ranges starting at row 2 does not matter. row 1 is blank anyway for the data produced.

If there is a way to make what I have here more efficient and get the two repeated columns in the right spot would be much appreciated.

Upvotes: 0

Views: 153

Answers (1)

Bob G
Bob G

Reputation: 106

If you select all the ranges at once you will not need to account for the shift in position for the next range. By specifying column ranges the "entirecolumn" is no longer require but can be there if desired.

As for the two repeated columns they simply need to be copied to the correct location. I have specified what to copy and the given the destination on the next line.

The below code seems to provide what you are asking for.

Sub mysub2()

    Range("B:B,E:F,H:H,J:AX,AZ:XFD").Delete

    Columns("D:D").Copy
        Columns("B:B").Insert Shift:=xlToRight
    Columns("C:C").Copy
        Columns("C:C").Insert Shift:=xlToRight
    
    Application.CutCopyMode = False
    
End Sub

Results

Upvotes: 1

Related Questions