VBA - How to Insert empty columns in excel

Question

I have an excel spreadsheet in which I am generating two columns L and M using VBA code. After generating two columns with data in it, I wanted to insert two new empty columns to the left of Column L. How can I do it?

As shown in the image, I want to add new empty columns to the left of L. So that the current Column L and Column M should be moved to Column N and Column O respectively.

My code is as follows-

Sub Wave()

 Dim N As Integer
 Dim Inp As Integer
 Dim Op As Integer
 Dim Src As Integer
 Dim MAX As Integer

 MAX = 1502
 Src = 0
 N = 0

 Op = ActiveSheet.Range("K2")
 For Src = 0 To MAX Step 1
   Inp = ActiveSheet.Range("K" & 2 + Src)
   Op = ma0 * Inp + ma1 * mx1 + ma2 * mx2 - mb1 * my1 - mb2 * my2
   mx2 = mx1
   mx1 = Inp
   my2 = my1
   my1 = Op

   ActiveSheet.Range("L" & 2 + Src) = Op
   ActiveSheet.Range("M" & 2 + Src) = ActiveSheet.Range("L" & 2 + Src) - ActiveSheet.Range("K" & 2 + Src)

 Next

End Sub

Record a macro that inserts a column, and then look at the code it generates. This should be the first thing you do when trying to write Excel VBA code - see if you can get Excel to do the heavy lifting for you. — Ken White
– Ken White, Commented May 3, 2016 at 2:41

teylyn · Accepted Answer · 2016-05-03 02:22:22Z

1

try

ActiveSheet.Columns("L:M").Insert Shift:=xlToRight

answered May 3, 2016 at 2:22

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dazzler Over a year ago

Thank you for the answer, perfect :)

ChrisB · Accepted Answer · 2020-04-01 18:48:04Z

0

You should disable CutCopyMode so that you don't inadvertently insert

Sub InsertRowAbove() ' ' InsertRow Macro ' Pushes active row down ' Dim N As Long

Application.CutCopyMode = False 'This line to get rid of clipboard.

ActiveCell.EntireRow.Insert Shift:=xlDown
N = Cells(Rows.Count, "G").End(xlUp).Row
Range("G4").Copy Range("G5:G" & N)
Range("J4").Copy Range("J5:J" & N)

End Sub

answered Apr 1, 2020 at 18:48

ChrisB

3,2735 gold badges42 silver badges68 bronze badges

Collectives™ on Stack Overflow

VBA - How to Insert empty columns in excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related