Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Help with Code

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Code

    Hi All

    I need some help with this code. I'd like to be able to multi select various cells in row 7 and get the code to select the respective columns. Also it appears that code only selects the column to the last cell with content, I'd like it to select the column/s to say row 500

    Sub SelectColumn()
    'Updateby20140510
    Dim xColIndex As Integer
    Dim xRowIndex As Integer
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(7, xIndex), Cells(xRowIndex, xIndex)).Select
    'Selection.Delete Shift:=xlToLeft
    End Sub

    I hope that makes sense - any help/suggestions would be much appreciated


    Regards
    Last edited by verada; 2015-10-15 at 00:06.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    Your code starts in the active column. How do you determine what the "respective columns" are?

    cheers, Paul

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Paul

    I was thinking of the shift click on various cells along row 7 and these would then determine what the "respective columns" are.

    I hope that makes sense

    Regards

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi verad
    Do you mean a continuous range as in Shift+Click or discontinuous with control +click?

    If a continuous selection then what about something like this as something different.
    You pas in the starting cell, columnOffset and rows required.
    If it looks likely then some tidying up is required.
    Code:
    Sub main()
        defineSelection "A7", 5, 100
    End Sub
    
    Sub defineSelection(rStart, colOffset, rowCount)
        Range(rStart).Select
        Range(ActiveCell, ActiveCell.Offset(0, colOffset)).Select
        Range(Selection, Selection.Offset(rowCount, 0)).Select
    End Sub
    Cheers
    Geof
    Last edited by geofrichardson; 2015-10-15 at 04:37.

  5. The Following User Says Thank You to geofrichardson For This Useful Post:

    verada (2015-10-15)

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like this?

    Code:
    Sub SelectColumn()
        Dim rArea                 As Range
        Dim rSel                  As Range
        For Each rArea In Selection.Areas
            If rSel Is Nothing Then
                Set rSel = rArea.EntireColumn.Resize(500)
            Else
                Set rSel = Union(rSel, rArea.EntireColumn.Resize(500))
            End If
        Next rArea
        rSel.Select
        'rsel.Delete Shift:=xlToLeft
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following 2 Users Say Thank You to rory For This Useful Post:

    geofrichardson (2015-10-15),verada (2015-10-15)

  8. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Geof

    Sorry I meant discontinuous with control +click.

    Looks like your code is pretty close - just needs to take into account the discontinuous with control +click cell selection and then selecting (and deleting) the respective column/s.

    Cheers

  9. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory - that looks very close also.

    I've attached a smaple of the spread sheet to give you (and any others) a better idea of what I'm tring to get the code to achieve.

    I've highlited Cells H2, J2 & L2 as I would using the discontinuous with control +click cell selection.

    I'd like the code to delete the entire column including the cell selections.

    Hope this helps
    Attached Files Attached Files

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Verada,

    A slight twist. This code will append the selection of the columns as you select any cell in row 7. Click any cell in any other row to clear the selection.

    Place in the worksheet module:
    Code:
    Private strng As String
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 7 Then
        col = Target.Column
        If strng = "" Then
            strng = Range(Cells(1, col), Cells(500, col)).Address
        Else:
            strng = strng & "," & Range(Cells(1, col), Cells(500, col)).Address
        End If
        Application.EnableEvents = False
            Range(strng).Select
        Application.EnableEvents = True
    Else:
        strng = ""
    End If
    End Sub
    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-10-15 at 21:37.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-10-15)

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I've highlited Cells H2, J2 & L2 as I would using the discontinuous with control +click cell selection. I'd like the code to delete the entire column including the cell selections.

    Verada,

    If you want to delete the columns as you select instead of having to manually run a macro, place this code in the worksheet module. If you select any cell in row 2, the column of the section will be deleted. Continue to delete as many columns as needed
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Row = 2 Then
            Target.EntireColumn.Delete
        End If
    End Sub
    Maud

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-10-15)

  14. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Maud

    That look really good, only issue is that I need to delete the column including row 2 but keep the row 1 as it was.

    I guess I wasn't clear when I indicated "Id like the code to delete the entire column including the cell" sorry about that.

    Also, can is there anyway that the normal "Undo" option is available in case a column is deleted in error?

    Your assistance is very much appreciated

    Regards
    Last edited by verada; 2015-10-15 at 22:36.

  15. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud,

    Sorry I don't really get this one

    What do you mean append the selection?

    Thanks again for your help

    Regards

  16. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You'll lose the undo stack whenever you run code that alters the sheet.

    What exactly do you mean by deleting the column? Do you mean just clearing the contents or actually deleting the cells? If the latter, which way should cells shift - up or left - to fill the space?
    Regards,
    Rory

    Microsoft MVP - Excel

  17. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Rory, Thanks for the reply

    May need to include a message in the code like "are you sure...?"
    Sorry I want very clear in the deleting column - what I'm after is the column from the cell you select, say A7, all cell (including A7) down to say 500 are selected and the and a shift left happens to fill the space.

    Hope that helps

    Regards

  18. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I'd like the code to delete the entire column including the cell selections.
    I need to delete the column including row 2 but keep the row 1 as it was.
    what I'm after is the column from the cell you select, say A7, all cell (including A7) down to say 500 are selected and the and a shift left happens to fill the space.
    Verada,

    I have overcome the "Undo" issue by having the code store the column prior to deleting the data making it retrievable. But I am confused exactly what you want to delete. Can you clarify?

    Maud

  19. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - Thanks again for your help.

    Sorry I'll try to clarify.

    Lets say I need to remove all the cells from A7 to A500 (but leave A1 - A6 as they are). I click in A7 that highlights all cells to A500 (but could be any column/s) and the shift left would occur. This is the code from the macro I recorded - Selection.Delete Shift:=xlToLeft

    I hope that helps

    Much appreciated.

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

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