Results 1 to 5 of 5

Thread: Insert Or Delete Columns Based On User Input

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0

    Insert Or Delete Columns Based On User Input

    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.

    Code:
    Code:
    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
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    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!!

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    Excel Fox this is excellent!!! Thank you so much. Outstanding!!!

Similar Threads

  1. Delete Rows Based on Conditions
    By AbiG2009 in forum Excel Help
    Replies: 6
    Last Post: 12-26-2018, 01:24 PM
  2. Macro To Insert Columns In Excel
    By jac3130 in forum Excel Help
    Replies: 2
    Last Post: 05-17-2013, 07:49 AM
  3. Replies: 4
    Last Post: 03-22-2013, 01:47 PM
  4. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  5. Format Cells Based on Given Input
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 10
    Last Post: 08-23-2011, 11:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •