Inserting loads of columns or rows in Microsoft Excel is awkward and vulnerable to errors. When you should enter dozens or extra columns, use this VBA process for ease and accuracy.

Picture: iStock/AndreyPopov

Inserting columns and rows in Microsoft Excel is a standard activity, and thankfully, it is easy, so long as you are not attempting to enter too many columns (or rows) on the identical time. As soon as your choice strikes off display, the choice course of is awkward, and it is easy to make errors. Strive inserting 20 columns manually, and you may see what I imply. Now think about inserting 100 or 500! When you want to take action even as soon as, you will wish to use the VBA process on this article as an alternative!

SEE: 83 Excel suggestions each person ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should utilize earlier variations. Use the downloadable .xlsx, .xls, or .cls information to acquire the code. Excel On-line does not help macros.

How you can insert columns manually in Excel

Inserting a column or perhaps a few columns is straightforward, and also you’re in all probability accustomed to the method already. If that’s the case, be at liberty to skip to the following part. In any other case, let’s proceed.

The method is easy: Choose the column to the correct of the place you plan to insert a brand new column. Proper-click the choice and select Insert. If you choose one column, Excel will insert one column. If you choose three columns Excel will insert three columns. The identical routine additionally works when inserting rows.

Now, let’s insert one column between columns C and D in any clean sheet as follows:

  1. Start by choosing column D—the column to the correct of the place you wish to insert the brand new column. To pick a complete column, click on the column header (the grey cell with the letter D).
  2. Proper-click the chosen column D.
  3. Select Insert from the ensuing submenu (Determine A).
  4. Determine B exhibits the brand new column. The orange column was D; now it is column E. Nothing occurs to column C. Discover additionally that Excel updates the column header cells appropriately. 

Determine A

excelcolumnsmacro-a.jpg

Determine B

excelcolumnsmacro-b.jpg

  The orange column is now column E.

When you change your thoughts, press Ctrl + Z to delete the column(s). You’ll by no means want a particular process to enter just a few new columns. However what if you should insert 10, 15 and even 100 new columns? Deciding on that many columns to the correct can be awkward at greatest since you’ll be scrolling off display to entry all of them. Maintaining with what number of columns you choose is tough. Below these circumstances, you may wish to use a VBA process as an alternative.

How you can use the VBA process to insert columns in Excel

The process in Itemizing A works with the chosen cell and performs precisely because the handbook course of does. First, the macro defines a few integer variables, i and j: i shops an enter worth that identifies the variety of columns to insert, and j is a counter. The following line makes use of the ActiveCell property to pick the whole column, primarily based on the cell the person clicks earlier than working the process. The for-loop then makes use of the enter worth, i, to insert the suitable variety of columns to the left (xlToLeft) of the chosen column. The process is flexible sufficient to additionally insert to the correct of the chosen column. Merely use xlToRight as an alternative of xlToLeft (within the for loop).

Itemizing A

Sub InsertColumns()

‘Insert a number of columns. Consumer enters the variety of columns needed.

Dim i As Integer

Dim j As Integer

ActiveCell.EntireColumn.Choose

‘Consumer inputs variety of columns to insert.

i = InputBox(“Please enter the variety of columns to insert”, “Insert Column(s)”)

‘Loop counts the variety of columns to insert.

For j = 1 To i

    Choice.Insert Shift:=xlToLeft ‘xlToRight

Subsequent j

Finish Sub

When you’re utilizing a ribbon model, remember to save the workbook as a macro-enabled file or the process will not run. When you’re utilizing a menu model, you possibly can skip this step.

To enter the process, press Alt + F11 to open the Visible Primary Editor. Within the Challenge Explorer to the left, choose ThisWorkbook so you possibly can run the process in any sheet. You’ll be able to enter the code manually or import the downloadable .cls file. As well as, the macro is within the downloadable .xlsx and .xls information. When you enter the code manually, do not paste from this net web page. As an alternative, copy the code right into a textual content editor after which paste that code into the ThisWorkbook module. Doing so will take away any phantom net characters which may in any other case trigger errors.

Now it is time to use the process to insert just a few columns. Earlier than executing the macro, click on a cell within the column to the correct of the place you wish to insert the column. Let’s use the process to insert 10 columns between columns C and D:

  1. Click on any cell in column D.
  2. Click on the Builders tab.
  3. Click on Macros within the Code group.
  4. Within the ensuing dialog, select InsertColumns(), proven in Determine C and click on Run.
  5. When prompted, enter 10 (Determine D), after which click on OK.

Determine C

excelcolumnsmacro-c.jpg

Determine D

excelcolumnsmacro-d.jpg

  Enter the variety of columns to insert.

As you possibly can see in Determine E the process enters 10 columns between columns C and D. This does not seem to be a giant deal proper now since you may do that manually with out an excessive amount of effort, however think about coming into 20, 50 or extra columns. This process saves you loads of effort and time when you should insert many columns. In contrast to the handbook course of, you possibly can’t undo the insert by urgent Ctrl + Z. 

Determine E

excelcolumnsmacro-e.jpg

  The process inserted a number of new columns.

Bonus tip

This process gives no error dealing with. Proper now, if a person enters something aside from a numeric digit, the process returns an error. The downloadable demonstration information have a second process that comprises easy error dealing with. This file additionally comprises a process for inserting a number of rows above or under the chosen cell. 

It is unlikely that you will wish to work via all these steps each time you wish to run the process. As an alternative, add the macro to the Fast Entry Toolbar. To take action, learn How you can add Workplace macros to the QAT toolbar for fast entry.

Additionally see

Source link