Inserting column range in vba

Question

I have a report in which I have to insert a new column between the last and second to last columns. However, when I try to insert the the column, I get a type mismatch error.

Dim columnInsert As Range
Dim rangeAverage As Range

Set columnInsert = Range("X80:X95")
Set rangeAverage = Range("AA1")

Columns(columnInsert:rangeAverage).Insert Shift:=xlToRight

The problem pointed out by the editor is this line:

Columns(columnInsert:rangeAverage).Insert Shift:=xlToRight

Basically all I want to do is insert 15 long blank column between AA1 and Z, but I keep running into problems.

paul bica · Accepted Answer · 2017-07-04 04:38:46Z

1

The range you entered in your .Insert statement is incorrect

Columns(columnInsert:rangeAverage) translates to Columns(Range("X80:X95"):Range("AA1"))

This will shift only the cells in the range specified:

columnInsert.Insert Shift:=xlToRight    'shifts only the cells from X80:X95 to right
rangeAverage.Insert Shift:=xlToRight    'shifts cell AA1 to right

This will shift the entire column AA to the right

Columns(rangeAverage).Insert Shift:=xlToRight
Columns(rangeAverage.Column).Insert Shift:=xlToRight

Any of the statements bellow insert a blank column between Column Z and AA:

Columns("AA:AA").Insert Shift:=xlToRight
Columns("AA").Insert Shift:=xlToRight
Columns(27).Insert Shift:=xlToRight         'column index

You can also insert multiple columns:

Columns("Z:AA").Insert Shift:=xlToLeft

This inserts 2 empty columns to between column Y and Z

edited Jul 4, 2017 at 4:38

answered Jul 4, 2017 at 4:10

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matthew Over a year ago

Ahhh I see, that makes much more sense. Thanks paul!

paul bica Over a year ago

I'm glad it helped. You should also start qualifying your ranges with the worksheet you intend to update, so if you work on Sheet1 and somehow Sheet2 will become active your changes will apply the Sheet2 instead of the intended Sheet1. You can prefix the statements like this Worksheets("Sheet1").Columns(27).Insert Shift:=xlToRight

Matthew Over a year ago

Ok, so I am now trying to refer to a named range for column AA, but when I try to set it to a variable I keep getting a type mismatch. Is there a way I can post the code here? I'm new to stackOverflow, I'm not sure if this is how things are supposed to be done :(

paul bica Over a year ago

If the initial issue is fixed it would be better to accept the answer then open a new question for the next issue with more details about the named range and the code

Collectives™ on Stack Overflow

Inserting column range in vba

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related