VBA: Select range of cells in a row until non empty cells (from active cell)

  • Dear All,


    I am looking for VBA code which will select entire range in a row from active cell until last non empty blank cell.


    To elaborate further, in first row the range of cell begins from D1 to AA1 (there are blank cells in this range). At any moment if I run the VBA code, from active cell until AA1, the selection should be copied for further process.


    It's my sincere request to help me in providing the VBA code.


    Thanks for your time & support in advance.


    - Billy

  • Hello,


    You could test following


    Code
    Sub TestSelect()
    Dim lastcol As Long
    lastcol = ActiveSheet.Cells(ActiveCell.Row, Application.Columns.Count).End(xlToLeft).Column
    Range(ActiveCell, ActiveCell.Offset(0, lastcol - 1)).Select
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Or

    Code
    Sub test()
        Dim r As Range, lCol As Long
        With ActiveSheet
            lCol = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column
            Set r = ActiveCell.Resize(, lCol - ActiveCell.Column + 1)
        End With
        r.Select
    End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • A couple more possibilities.

    Code
    Sub Maybe_A()
        Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column)).Select
    End Sub


    Code
    Sub Maybe_B()
        Cells(Selection.Row, Selection.Column).Resize(, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column - Selection.Column + 1).Select
    End Sub


    But you mentioned that the selection should be copied for further processing.
    Where do you want it to end up (pasted)?
    You should avoid selecting if not absolutely required and it sounds like you don't need to select here. As long as we know where to transfer that range to.

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • A couple more possibilities.

    Code
    Sub Maybe_A()
    Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column)).Select
    End Sub


    Code
    Sub Maybe_B()
    Cells(Selection.Row, Selection.Column).Resize(, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column - Selection.Column + 1).Select
    End Sub


    But you mentioned that the selection should be copied for further processing.
    Where do you want it to end up (pasted)?
    You should avoid selecting if not absolutely required and it sounds like you don't need to select here. As long as we know where to transfer that range to.


    Thanks for your reply again.


    The complete operation requires to select the cell as per above requirement, offset the selected range to one row below, copy this newly selected range & paste it another spreadsheet.


    - Billy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!