Share via

Insert Columns via VBA

ScottGem 68,810 Reputation points Volunteer Moderator
2016-03-25T19:09:05+00:00

This is driving me batty. I.m setting up a spreadsheet that is taking data from Access. Once the data is placed in Excel I need to massage it a bit. part of that massaging is to insert some columns. So I carefully recorded a macro to go through the steps of selecting the columns, then entering the column headers. and saving the macro. I then deleted the inserted columns and ran the macro. Worked perfectly. 

So next, I tried to tweak the macro a bit to make it more dynamic and it stopped working at selecting the right range of columns.

The original recorded code was:

    Columns("X:AI").Select

    Selection.Insert Shift:=xlToRight

I added these two lines:

    Dim intStartYr As Integer

    intStartYr = Year(Date) - 6

Now, when I run the macro it select X:CO and I have no clue why. I even tried recording the macro and it still does the same. 

I'm about to give up and just have the user do it manually. But I figured I'd ask here to see if anyone can shed some light on this.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2016-03-25T19:39:14+00:00

    Can't really tell anything from the limited amount of code you are posting.  I suspect the reason has nothing to do with the two lines of code you are adding, though.

    I know the macro recorder throws in a lot of Selects (since that is what you are doing with the cursor), but there is rarely need for Selecting.

     Columns("X:AI").Insert shift:=xlToRight

    would be more efficient.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2016-03-25T20:32:18+00:00

    Thanks again, For what I'm doing, the code doesn't necessarily have to be too efficient. Basically I'm inserting some columns and filling them with formulas. Once I get everything working I will look at streamlining the code. 

    If I had my way I would do this all in Access. But the user wants it in Excel where he can "play" with the data.

    0 comments
  2. Anonymous
    2016-03-25T20:14:09+00:00

    Glad you have things working OK.  But there is probably something in that code, or in the Excel environment (or how the code has modified that environment, to explain the Selection you are seeing.  If you are relying on the macro recorder, though, efficient code is not its forte :-)

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2016-03-25T20:05:01+00:00

    Perfect Ron, that seems to have done the trick. It also taught me a bit about working with Excel VBA. I see what you mean about the macro recorder and SELECTs. I know the Access object model extremely well. But the Excel object model has proven somewhat mysterious for me. 

    I didn't post the rest of the code, because the rest of the code all works fine. When I stepped through, as soon as it executed the columns command, it highlighted all the columns from X:CO.

    0 comments