Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    201
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Use Ctrl Click to select non contIgous columns

    I am trying to write a VBA routine that will allow the user to select non contagious columns. The problem is at the start I do not know how many columns will need to be selected. I could step through the procedure one at a time but would prefer to select them all first.

    I have tried using the union function in a very clumsy fashion. The example code below skips if 3 columns are not selected and goes to 2 of course if I had not selected 2 columns I would have an error. I could expand the lines that create the union and skip past each one on error but that is where clumsy comes in.

    Code:
    Sub ctrlselect()
        Dim ColS(), Y, Check
        Y = 0
        Do Until Check = vbNo
        Y = Y + 1
        ReDim Preserve ColS(Y)
        Set Col1 = Application.InputBox("Select Cell in Column", Type:=8)
        ColS(Y) = Col1.Column
        Check = MsgBox("Any More Columns", vbYesNo)
        Loop
        On Error Resume Next
        Application.Union(Columns(ColS(1)), Columns(ColS(2)), Columns(ColS(3))).Select
        Application.Union(Columns(ColS(1)), Columns(ColS(2))).Select
    End Sub
    Thanks for any suggestions, I am working on a Mac so its really cmd+click

  2. #2
    Super Moderator rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,452
    Thanks
    3
    Thanked 262 Times in 240 Posts
    Application.Inputbox will allow them to select multiple non-contiguous cells, so why do you need the loop?
    Regards,
    Rory

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    201
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Because I do not know how many columns I need to select. So I am using the loop to create a string for the union command. I think I nearly have it and will post my result in case anyone is interested. Thanks for getting back to me,

  4. #4
    Super Moderator rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,452
    Thanks
    3
    Thanked 262 Times in 240 Posts
    I don't follow. You don't know the columns at the start of the code but you do know within the loop?
    Regards,
    Rory

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    201
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    I don't follow. You don't know the columns at the start of the code but you do know within the loop?
    The loop is because I do not at the outset know how many columns I need (in this case to delete). I
    needed to build a dynamic Union function and that was giving me problem. Now fixed and in case anyone is interested my working code follows. It has more than than the problem I outlined but you can see the construct which still seems clumsy but not like it was.

    Sub SelectColtoDelete()
    Dim Col1, Check, ColsSel, Instruct
    Worksheets("Raw Data").Activate
    ReRun:
    Cells(1, 1).Select
    Instruct = MsgBox("" _
    & "This routine asks for" & Chr(13) _
    & "all the columns on" & Chr(13) _
    & "the current worksheet" & Chr(13) _
    & "To be deleted, proceed", vbYesNo)
    If Instruct = vbNo Then Exit Sub
    Instruct = MsgBox("" _
    & "Is there more than 1 column to delete", vbYesNo)
    If Instruct = vbYes Then
    'Get the Column Number for the 1st Column to be deleted
    Set Col1 = Application.InputBox("Select Cell in Column", Type:=8)

    Set ColsSel = Columns(Col1.Column)
    Do Until Check = vbNo
    Set Col1 = Application.InputBox("Select Cell in Column", Type:=8)
    Set ColsSel = Union(ColsSel, Columns(Col1.Column))
    Check = MsgBox("Any More Columns", vbYesNo)
    Loop
    Else
    Set Col1 = Application.InputBox("Select Cell in the single Column" & Chr(13) _
    & "to be deleted", Type:=8)
    Set ColsSel = Columns(Col1.Column)
    End If
    ColsSel.Select
    Check = MsgBox("Are these the correct columns", vbYesNo)
    If Check = vbNo Then
    ColsSel = ""
    GoTo ReRun
    Else
    ColsSel.Delete
    End If
    Cells(1, 1).Select
    End Sub

  6. #6
    Super Moderator rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,452
    Thanks
    3
    Thanked 262 Times in 240 Posts
    You still haven't explained (as far as I'm concerned) why you need a loop when you can run Application.Inputbox once and select multiple columns in it.
    Regards,
    Rory

  7. #7
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    201
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thank you again, simply put lack of skill, I had tried using application.input with multiple selections, but when I selected the result I just got the individual cells . The bit I now discover was missing was "entirecolumn". I did say it was clumsy and I guess that was an understatement I will amend my code.

    Regards

    Peter

Posting Permissions

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