Range(activecell, Activecell.end(xlright)).copy

    Does this code copy all cells from the active cell up to the last non-blank cell, or is it up to the first blank cell after the last non-blank cell? Thanks

    Re: Range(activecell, Activecell.end(xlright)).copy


    As it is it will no nothing but throw an error.


    Change it to this
    [vba]Range(activecell, Activecell.end(xlToRight)).copy[/vba]


    and from the active cell it will copy either
    if active cell contains data - all cells up to but not including the first empty cell in the active row


    if activecell is empty - all cells up to and including first non empty cell or last column which ever comes first.

    [h4]Cheers
    Andy    [/h4]

    Re: Range(activecell, Activecell.end(xlright)).copy


    Thanks, Andy, for that clarification... But now I have a real problem - for my overall program, I need my code to be able to copy Cell A5 and ALL cells to the right of cell A5 right up to the last non-blank cell in that row - and there WILL be empty, possibly non-consecutive cells scattered in between cell A5 and the last non-blank cell in the row.


    Also, I need the code to just copy all these cells on an 'as is' basis - without deleting the empty cells in between.


    Pls help... Thanks

    Re: Range(activecell, Activecell.end(xlright)).copy


    Rather than going right you need to go left from the last column in that row.
    [vba] Range(ActiveCell, ActiveCell.EntireRow.Cells(1, Columns.Count).End(xlToLeft)).copy[/vba]


    If you think a last column of a row might be occupied then you would need some extra code to check that.

    [h4]Cheers
    Andy    [/h4]

    Range(activecell, Activecell.end(xltoright)).copy


    I need a variation of the code


    Code
    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy


    that will effectively allow me to copy the current active cell and ALL cells to its right up to and including the LAST non-blank cell in that particular row - pls note that any cell (incl. ActiveCell) in the range (ActiveCell, ActiveCell.End(xlToRight)) may be blank/empty, which is why the above code, as is, will not work.


    Note that the current active cell is not fixed either in terms of its row number or its column number, so that ActiveCell could just as easily be D46 as AB7. Thanks.

    Re: Range(activecell, Activecell.end(xltoright)).copy


    rather than start another thread why not reply to my post stating that it does not work.


    You will need to expand on how you used it as I did test before posting.

    [h4]Cheers
    Andy    [/h4]

    Re: Range(activecell, Activecell.end(xlright)).copy


    Hi Andy,


    So hypothetically, if my current Excel active cell is AM5, and I need to copy ALL cells from AM5 to IV5 (Column IV is last column in any Excel worksheet, and let's assume there's a non-blank value in cell IV5 in this example while cell AM5 is empty, and several random cells between AM5 and IV5 are also empty),


    Code
    Range(ActiveCell, ActiveCell.EntireRow.Cells(1, Columns.Count).End(xlToLeft)).copy


    will still work as is? Note that cells A5 to AL5 are all filled with different types of info, but I need to copy only cell AM5 and all cells to its right (incl. the last filled cell in the row)


    Thanks in advance

    Re: Range(activecell, Activecell.end(xlright)).copy


    If the end cell could contain something then you need to test for that.


    [vba] If Len(ActiveCell.EntireRow.Cells(1, Columns.Count)) = 0 Then
    Range(ActiveCell, ActiveCell.EntireRow.Cells(1, Columns.Count).End(xlToLeft)).Copy
    Else
    Range(ActiveCell, ActiveCell.EntireRow.Cells(1, Columns.Count)).Copy
    End If[/vba]


    If AM5 is the activecell then any thing to the left, A5 or AL5 will not be copied.

    [h4]Cheers
    Andy    [/h4]

Participate now!

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

Register Yourself Login