insert VARYING blank column with two intervals,

[markDuffy]

Hi Guys
I want to insert columns into my sheet. It has some columns filed say 10. I have a VBA formula which inserts one column. I cant work out how to alter it to add say 3 blank columns. I know that the 30 will need to be increased to allow for more columns and it would be sweet if this was automatic. Have not loaded an example but if you want one please ask. I got this code from "http://www.extendoffice.com/documents/excel/798-excel-insert-blank-column-every-other-column.html"

Range("A1").Select
            For colx = 2 To 30 Step 2
            Columns(colx).Insert Shift:=xlToRight
    Next

Any suggestions
Mark

[LokeshKumar]

Hey,

Hope this will help you.....

Sub InsertColumns()
    
    Dim Ctr As Integer
    Dim ColNumber As Integer

'You can put ColNumber as numric or as well text like a,b,c,d,e
        ColNumber = 5
        
'3 means it will add 3 columns in Columns(5)=Columns(E)
        For Ctr = 1 To 3
            Columns(ColNumber).Insert Shift:=xlToRight
        Next Ctr
        
End Sub

[markDuffy]

Hi Lokesh
Hope all is well with you in India. I am in London and today the sun is shining but my office is in my basement. I appreciate your help. Thank you.

You variation works great but is not precisely what I am looking for. I have a varying number of entered columns (say 10) and I want to insert blank columns starting at column B. Your VBA inserts only once not to the end of the entered columns.
Note the existing example will insert one column for as many columns as are full.
Does this make sense or should I upload and example?

Mark

[LokeshKumar]

Hi Mark
I am good too...
I just paste the code for your understanding. Try the following code....see it works for you or not?

Option Explicit
Sub InsertColumns()
    
    Dim Ctr As Integer
    Dim ColInsert As Integer
    Dim ColName As String
    Dim ColPos As Variant
    Dim RunAgain As Integer

        
        Application.ScreenUpdating = False
            Do
StartPoint1:
                ColName = InputBox("Enter the Column Name.", "Insert Columns", "A")
                
                    If IsNumeric(ColName) Then
                        MsgBox "Column Name should be Text only" & vbNewLine & "For example A, B, C Etc.", vbCritical
                        GoTo StartPoint1
                    End If
StartPoint2:
                ColPos = InputBox("Enter the Column Position (Right,Left).", "Insert Columns", "Right")
                    
                    If LCase(ColPos) = LCase("Right") Or LCase(ColPos) = LCase("Left") Then
                    
                    Else
                        MsgBox "Column Position should be Left/Right!", vbCritical
                        GoTo StartPoint2
                    End If
                    ColPos = "xlTo" & ColPos
                
StartPoint3:
                ColInsert = InputBox("How many columns need to be inserted?", "Insert Columns", 2)
                    
                    If Not IsNumeric(ColInsert) Then
                        MsgBox "It should be numeric number only!", vbCritical
                        GoTo StartPoint3
                    End If
        
        Application.ScreenUpdating = True
        
                For Ctr = 1 To ColInsert
                    
                    If WorksheetFunction.Proper(ColPos) = "Left" Then
                        Columns(ColName).Offset(0, 1).Insert Shift:=xlToRight
                    Else
                        Columns(ColName).Offset(0, 1).Insert Shift:=xlToLeft
                    End If
                    
                
                Next Ctr
                
                RunAgain = MsgBox("Do you want to insert more column/s?", vbYesNo, "Excel")
            
            Loop Until RunAgain = vbNo

End Sub

Here is the attachment..
Mark.xlsm

Thanks
Lokesh K.

[markDuffy]

Hi Lokesh

The information you have very kindly sent me is excellent. however I have not explained correctly what I am looking for. The only difference is that I would like to be able to stipulate the number of blanks and then for the program to run till there were no more entered columns to insert between. I may have up to 100 columns between which i want to add a certain number of blanks columns. I have copied a macro into the file you made and am uploading it again. You will see what I mean. In the mean time i will go back to studying VBA. I have an awful lot to learn to be even close to these on this forum.

Mark

[LokeshKumar]

I got it your code and it is working correctly.....however you need to declare colx as integer/long...