Insert Or Delete Columns Based On User Input

[HDMI]

Hello forum,

I'm working with this macro to add and delete columns base on a number entered into a cell on another spreadsheet. Please see attachment template example and code.

On the example attachment on the "Employees" sheet there are numbered columns starting from 1 thru 6 at columns H. I need a macro that will add more columns or delete columns indicated by the number inputted in cell C9 on the "Monitoring Info" sheet. For instance I input the number 50 in the "Monitoring Info" sheet in cell C9 the "Employees" sheet will show starting from column H the number of columns 1 Thru 50.
The column will be incremented or diminish based on the user input on the Monitoring Info page cell C9. If the user input 5 into cell C9 then only 5 column will show numbered 1 through 5 starting at column H1 on the "Employee" sheet. If the user inputs 10 into cell C9 the number columns will show 1 thru10 starting at H1 on the Employee" sheet. etc, etc. I'm new to this forum I'm trying to learn all this and the people that know how to do this like yourself I think are very smart and intelligent and I hope to learn also. Thank you all once again.



Sub Columninput()
Dim MyRange As Object
' Store the selected range in a variable.
Set MyRange = Selection
' Select the entire column.
Selection.EntireColumn.Select
' Insert Columns in all selected sheets.
Selection.Insert
' Reselect the previously selected cells.
MyRange.Select

End Sub

[Excel Fox]

hi HDMI, welcome to Excel Fox

Yes, this can be done. However, you need to mention whether you'll be doing that on the empty template, or whether the data range will be filled. So for example, if you already have information filled in say H to M columns, and then you say you want to insert 10 columns, then the remaining 4 columns would need to be entered 'BETWEEN' one of column H:M, to maintain consistent formatting. Otherwise, a lot more code will need to be written for maintaining format also. So can you clarify whether you'll be running the code on an empty template, or a filled one

[HDMI]

Thank you so much for responding. The Template will be filled and you are correct the last four column will always have to be include whether if it is one column or 5000 hopefully I will never have 5000 but just in case. :-) Thank you again!!

[Excel Fox]

Here's the code. By the way, you need to remove the trailing space in the Employees sheet tab. Otherwise, the code below will throw an error.


Sub Columninput()

Dim lng As Long
Dim lngTotalEmployees As Long
Dim lngCol As Long

For lngCol = 8 To Worksheets("Employees").Cells(1, Columns.Count).End(xlToLeft).Column
lngTotalEmployees = lngTotalEmployees + 1
If Worksheets("Employees").Cells(1, lngCol).Value <> lngTotalEmployees Then
Exit For
End If
Next lngCol
lngTotalEmployees = lngTotalEmployees - 1
lng = Worksheets("Monitoring Info.").Range("C9").Value
If lng > lngTotalEmployees Then
For lngCol = 1 To lng - lngTotalEmployees
Worksheets("Employees").Columns(8 + lngTotalEmployees - 1).Insert xlToRight
Worksheets("Employees").Cells(1, 8 + lngTotalEmployees - 1).Value = lngTotalEmployees + lngCol
Next lngCol
With Worksheets("Employees").Sort
.SortFields.Clear
.SortFields.Add Key:=.Parent.Range("H1").Resize(, lng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange .Parent.Range("H1").Resize(, lng)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
ElseIf lng < lngTotalEmployees Then
Worksheets("Employees").Columns(8 + lng).Resize(, lngTotalEmployees - lng).Delete xlToLeft
Else
MsgBox "No change!", vbOKOnly, "Delete Employee Columns"
End If

End Sub

[HDMI]

Excel Fox this is excellent!!! Thank you so much. Outstanding!!!