Worksheet coding

[bknight]

I've got a worksheet that has code in it, namely to rearrange how the columns are presented. This worksheet is used as a link in Access.

Code: Select all

Sub RemoveBlanks()
    Dim r1 As Long
    Dim r2 As Long
    Dim rgLastR As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
     Columns("C:C").Select
     Selection.Cut
     Columns("I:I").Select
     Selection.Insert Shift:=xlToRight
     Columns("G:G").Select
     Selection.Cut
     Columns("D:D").Select
     Selection.Insert Shift:=xlToRight
     Columns("F:F").Select
     Selection.Cut
     Columns("E:E").Select
     Selection.Insert Shift:=xlToRight
     Cells.Select
     ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
         , 7, 8, 9, 10, 11), Header:=xlYes
     Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    

This has worked for years but now the data has changed format and I need to change the code to accommodate the remaining code.
There used to be a company name in column B, but that information doesn't exist in the data. This field was used in only one query, so I deleted that reference. I MAY NEED to insert a blank column at B, but I believe that won't be necessary.
If I were to insert a blank column what would the code lines be?

[HansV]

If you deleted column B, i.e. columns C, D, E, etc. shifted to the left, the references in the code to those columns will be off, so you'd either have to update all those references, or insert an empty column in column B. You'd have to do that only once, so it's not really necessary to do that in the code. But if you prefer to do so, insert

Code: Select all

    Range("B1").EntireColumn.Insert

below the line

Code: Select all

ActiveSheet.AutoFilterMode = False

Since column B is now blank, you don't have to remove duplicates from it, so you can remove column index 2 from the RemoveDuplicates instruction:

Code: Select all

     ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3, 4, 5, 6 _
         , 7, 8, 9, 10, 11), Header:=xlYes

[bknight]

Thanks

[bknight]

Now in a year or so the former companies in column B will all be gone as I delete data greater than one year. Once that is done column B will be trivial, and I may redesign the queries eliminating column B out of everywhere it exists today. But that is another story.