Solved: Inserting Blank Columns

[krishhi]

Hello Guys,

I have an Excel File which Contains Columns like

In Column A
Date
In column B
Description
In Column C
Material Cat.
etc.. Up to 25 Columns.

Here is my Query, I want VBA Code, to Insert a Blank Column after Every Existing Columns i.e. Date, Description, Material Cat. etc..

Any Clue Guys?

[PhilC]

Sure that's an easy fix. Try adding this code to your existing VBA code.
col = ActiveSheet.UsedRange.Columns.Count
For I = col To 1 Step -1
ActiveSheet.Columns(I).Insert Shift:=xlToRight
Next I
This code will grab how ever many columns you have (from column A to CU, 1/2 of the total available columns) and insert a blank column throughout the spreadsheet. The key is to remember to add them from the far right to the far left. You can do it the other way, from left to right, but the code starts getting cumbersome and doesn't fit into a for next loop as easily. Note: this code will only work as long as you have less than 1/2 of the total available columns to expand. Any more than 1/2 will result in an error. So that gives you ~125 columns worth of data that you can expand in this manner.

Enjoy
Phil C

[krishhi]

Sure that's an easy fix. Try adding this code to your existing VBA code.
col = ActiveSheet.UsedRange.Columns.Count
For I = col To 1 Step -1
ActiveSheet.Columns(I).Insert Shift:=xlToRight
Next I
This code will grab how ever many columns you have (from column A to CU, 1/2 of the total available columns) and insert a blank column throughout the spreadsheet. The key is to remember to add them from the far right to the far left. You can do it the other way, from left to right, but the code starts getting cumbersome and doesn't fit into a for next loop as easily. Note: this code will only work as long as you have less than 1/2 of the total available columns to expand. Any more than 1/2 will result in an error. So that gives you ~125 columns worth of data that you can expand in this manner.

Enjoy
Phil C

Thank you Very Much :bow:

[Blade Hunter]

Or you can do it without looping:


Range("A:A,B:B,C:C,D:D,E:E,F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

[krishhi]

Or you can do it without looping:


Range("A:A,B:B,C:C,D:D,E:E,F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Thanks for your help,

But It is very hard when you have Huge columns.:thumb