issue with inserting Columns

[bhandari]

Sub AddAColumn()
 Dim varUserInput As Variant
 Dim ColNum As Variant
 varUserInput = InputBox("Enter Column Number where you want to add a Column:", _
  "What Column?")
 If varUserInput = "" Then Exit Sub

ColNum = varUserInput
    Rows(ColNum & ":" & ColNum).Insert Shift:=xlDown
    Rows(ColNum - 1 & ":" & ColNum - 1).Copy Range("AV" & ColNum)
    Range(ColNum & ":" & ColNum).ClearContents
End Sub

Command Button 1
user need to give value in input box ,how many columns need to insert
if user give 2 in input box
2 columns need to insert after AV Column
Command Button 2
Delete Columns Through Input Box
if user give 1 column
one Column need to delete after "AV"
if user give more than 5 Columns i need to give restriction "Msg box need to pop up"
"we cant delete more than 5 columns" or any Alternative ?


Tnx In Advance

 

[Scott T]

The code you posted is inserting rows, do you want to insert columns or rows?

 

[bhandari]

The code you posted is inserting rows, do you want to insert columns or rows?

i want to insert Columns

I got a a similar code in internet.

 

[Scott T]

Is this what you want?

Sub addcolumns()
Dim numcol As String
numcol = InputBox("Enter number of columns to insert:", _
  "Number of Column?")
If numcol = "" Then
    MsgBox ("No value entered")
    Exit Sub
End If
Range("AW1").Resize(, numcol).EntireColumn.Insert
End Sub

Sub delcolumns()
Dim numcol As String
numcol = InputBox("Enter number of columns to deleat:", _
  "Number of Column?")
If numcol = "" Then
    MsgBox ("No value entered")
    Exit Sub
End If
If numcol + 0 > 5 Then
    MsgBox ("we cant delete more than 5 columns")
    Exit Sub
End If
Range("AW1").Resize(, numcol).EntireColumn.Delete
End Sub

 

[bhandari]

Working very Well..little bit missing in code

can you please Copy The data of "AV" Column to new inserted columns..Those are Inserting with Blank cells by using your code..

This thing is missing in code..

 

[Scott T]

Try

Sub addcolumns()
Dim numcol As String
numcol = InputBox("Enter number of columns to insert:", _
  "Number of Column?")
If numcol = "" Then
    MsgBox ("No value entered")
    Exit Sub
End If
Range("AW1").Resize(, numcol).EntireColumn.Insert
Columns(48).EntireColumn.Copy Range("AW1").Resize(, numcol)

End Sub

 

[bhandari]

Thank You So much its Helped Alot

 

[Scott T]

You are welcome