0

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.

user17420363
  • 35
  • 7
  • 1
    [How to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Cyril Feb 01 '22 at 13:27
  • You are deleting from left to right (standard order), but will want to go right to left (reverse order) as to not change the column order being deleted, thus changing your target column, e.g., `a b c` exist, delete `b`, now `a c` exist, but the old `c` is in position `2`. Given you have `G` after `F:AT` being deleted, I can only assume you've taken the removed column into account, but better to bring it up than not. – Cyril Feb 01 '22 at 13:34
  • As [Cyril](https://stackoverflow.com/users/3233363/cyril) states, don't remove from left to right, but from right to left: first remove the outmost right columns, then the second outmost right columns, .... – Dominique Feb 01 '22 at 13:36
  • Okay yes, I was not factoring in the order of deletion. This is helpful. I think my #1 priority is getting those repeated columns copied into the right spots. All other improvements would just help with speed... always a plus! – user17420363 Feb 01 '22 at 13:38

1 Answers1

1

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

Bob G
  • 96
  • 3