Inserting columns

[Gaijin]

I am sure there is an easy sollution to this problem, however I am not that good with VBA. What I am trying to do is insert a series of columns on a row corresponding to the number in the cell on the row in column A.

For example if column A contains a 3 in want ot insert 3 columns between A and B in that row only. I also need it to do this for the number of rows I select.

I searched the board but could not find anything like this.

 

[Jengor]

In the example below, which is only valid for row 1, put the code in a module in a trial worksheet, place any number in A1 and something in B1 (to check it works) :P then run the macro.

Sub Insertcells()
    Dim iInsert As Byte
    
    Range("B1").Select
    For iInsert = 1 To Range("A1").Value
        Selection.Insert Shift:=xlToRight
    Next
End Sub

If you want it to extend to other rows then you would need to specify whether it was for a set of rows or for every row containing a number in the A cell.

HTH

 

[Gaijin]

I need it to run for every row on the spreadsheet that contains a number in cell A. Thank you for your help.

 

[Gaijin]

bump back up

 

[TommyGun]

Try this and see if it gives desired results.

Sub InsertColumns()

Dim l As Long
Dim c As Integer

l = Range("A65536").End(xlUp).Row

For i = 1 To l

    If IsNumeric(Cells(i, 1)) Then
        c = Cells(i, 1)
        Range(Cells(i, 2), Cells(i, 1 + c)).Insert Shift:=xlToRight
    End If

Next i

End Sub

 

[Jengor]

Sorry for the delay in replying but Tea was called! In the meantime you have a perfect solution from TommyGun.

Best wishes

 

[Gaijin]

That is perfect. I guess I need to sit down and learn VBA one of these days. Mrexcel is 100% in coming up with solutions for me. As always thank you everyone.