Why would a range variable change when selected?

Question

The originally selected cell(s) are stored in rngStart to be re-selected at the end, so the user won't be transported away by the macro. However, the range stored in rngStart changes. Seemingly by itself. It ends up being the range where the paste operation happens.

Sub Macro2()
    Application.ScreenUpdating = False

    Dim rngStart 'The variable I'm struggling with
    Dim ws As Worksheet

    Set rngStart = Selection 'Store original selection
    Set ws = ActiveSheet

    Selection.Cut
    'Find an empty cell in column B
    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    ActiveSheet.Paste 'Upon executing this line, rngStart changes to the cell being pasted to
    rngStart.Select 'Supposed to return to the originally selected range

    Application.ScreenUpdating = True
End Sub

Just don't use `.Select` it is a very bad practice: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also there are much better ways to find the next empty cell than looping. — Pᴇʜ, Jul 19 '19 at 08:44
I do genuinely want to select, so the user's own selection will appear to have not moved. — ZFunhouse, Jul 19 '19 at 08:46
Ah it is because you moved the original range (by cut/paste) to the new position. So since the `rngStart` object references the range, and the range moved, it is actually the new position. — Pᴇʜ, Jul 19 '19 at 08:49
@Pᴇʜ Exactly what I was about to say, so instead of using `rngStart` at the end, why not just select a single cell where you want the user to end up. — Dean, Jul 19 '19 at 08:51

score 1 · Accepted Answer · answered Jul 19 '19 at 08:55

Save it as a String.

Sub Macro2()

    Application.ScreenUpdating = False

    Dim rngStart As String 'The variable I'm struggling with
    Dim ws As Worksheet

    rngStart = Selection.Address 'Store original selection
    Set ws = ActiveSheet

    Selection.Cut
    'Find an empty cell in row B
    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    ActiveSheet.Paste 'Upon executing this line, rngStart changes to the cell being pasted to
    Range(rngStart).Select  'Supposed to return to the originally selected range

    Application.ScreenUpdating = True

End Sub

score 0 · Answer 2 · answered Jul 19 '19 at 08:57

0

Remember your start range address to go back there. Also note that there is a much faster way to find the next empty row in a column:

Sub Macro2()
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim rngStart As Range
    Set rngStart = Selection

    Dim OriginalAddress As String
    OriginalAddress = rngStart.Address

    rngStart.Cut

    ws.Cells(ws.Rows.Count, "B").End(xlUp).Offset(RowOffset:=1).Select
    ws.Paste

    ws.Range(OriginalAddress).Select

    Application.ScreenUpdating = True
End Sub

answered Jul 19 '19 at 08:57

Pᴇʜ

56,719
10
49
73

`much faster way` - that way finds the *first empty cell after the last filled cell* (wrongly, btw: it will fail if there is no data in column B, or if there is data in the last cells of B). The OP's code looks for the *first empty cell inside the data* (which can be used to fill gaps), so that is not equivalent. – GSerg Jul 19 '19 at 09:02
@GSerg While you are technically right, I assumed that the column has no gaps as he pastes there (which would easily overwrite the data if there was following data). But yes technically you are right it finds the last used cell +1. – Pᴇʜ Jul 19 '19 at 09:07
@Pᴇʜ Thank you for your solution. Storing Address as a String works. – ZFunhouse Jul 19 '19 at 13:30

Why would a range variable change when selected?

2 Answers2

Linked