VBA - Inserting a variable number of columns based on the value of a cell

BenVan

New Member
Joined
Aug 22, 2012
Messages
3
Hi all,

It is the very first time I work with VBA and I am struggling quite a bit.

I have an Excel sheet in wich I have to insert a number of columns equal to a value in a cell. Note that the value in this cell will change over time. As a result, it is always the same cell that I would like to refer to. I have created a simple macro that inserts one single column in order to see the basic code. Unfortunately, I am unable to adjust it to arrive at the desired result.

Can anyone help me out?

Thanks
_________________

Sub Macro1()
'
' Macro1 Macro
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
can you attach a link to a samplefile with before and after situation ?
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
340
Office Version
  1. 2013
Platform
  1. Windows
Hi, BenVan,

we´re talking about action on the very same sheet? Here B1 is the cell to deliver the numbers of columns to insert (must be greater than 0). Code goes into a Standard module:

Code:
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Ciao
Holger
 

BenVan

New Member
Joined
Aug 22, 2012
Messages
3
Hi Holger,</SPAN>

Thank you very much for your answer. I am indeed talking about a reference cell in the same sheet.</SPAN>

I have tried to run the query that you submitted, but the following message pops up: </SPAN>

“Compile Error: </SPAN>

Expected: expression”</SPAN>

Am I doing something wrong?
</SPAN>
Also when I add "Sub Name ()" on top and "End Sub" at the end, it doesn't work.

Thanks again for your help.


Hi, BenVan,

we´re talking about action on the very same sheet? Here B1 is the cell to deliver the numbers of columns to insert (must be greater than 0). Code goes into a Standard module:

Code:
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Ciao
Holger
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
340
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi, BenVan,

if you just copied the code into a standard module the colour for this should turn red (by default) and release the note you mentioned.

What version of Excel do you use?= If I use the code with Excel2000 xlFormatFromLeftOrAbove throws up an error - I think this parameter was introduced either with 2002/XP or 2003.

If you don´t want the formats to be copied as well please try:

Code:
Public Sub BenVan_120823()
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight ', CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub
This codes needs to be started either from the VBE or from the Macro Window (ALT+F8) if the module hasn´t been declared private and as long as the code is in a standard module.

Ciao,
Holger
 

Ingolf

Banned
Joined
Mar 20, 2011
Messages
809
Hi,

For me HaHoBe code work fine. You have to put a number in B1.

Try:

Code:
Sub InsertColumns
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,488
Office Version
  1. 365
Platform
  1. Windows
Try this one too. If B1 contains a negative number or text, no columns will be inserted.
Code:
Sub Insert_Columns()
    On Error Resume Next
    Columns("C").Resize(, Range("B1").Value).Insert
    On Error GoTo 0
End Sub
 

BenVan

New Member
Joined
Aug 22, 2012
Messages
3
Hi Holger, Ingolf and Peter,

Thank you very much for your help.

It actually works now --> Problem solved.

Thanks again!

BenVan
 

Watch MrExcel Video

Forum statistics

Threads
1,129,523
Messages
5,636,820
Members
416,943
Latest member
kitkat22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top