Why does Excel inconsistently manage the cut rows range during a cut and paste to a new sheet?Cutting Row with Data and moving to different sheet VBAExcel VBA remove blank row after cutCut + Insert sometimes deletes and shifts cells, other times it doesn't. Why?In Excel VBA, how can I test if an Excel.Range object variable loses its reference without raising runtime error 424..?Cut Paste data from excel sheet using Autofilter in Excel VBAWeird Excel bug when deleting sheet with named ranges?Cut and Paste Columns in Excel Range with c#Search for text in a string, copy & paste rows to new sheetVBA Macro code fail; Copying and Pasting from range into new rangecopy data from only 3 sheets and paste it in new sheet - ExcelLoop through excel spreadsheets in different workbooksIf cell = closed project, cut cell and past into “Projects closed” worksheet on next available rowExcel VBA: Trigger macro on cut/paste/delete/insert eventsExcel VBA - Routine that finds on one sheet and paste on another moving destination cells down
Is it rude to call a professor by their last name with no prefix in a non-academic setting?
Count Even Digits In Number
Is the derivative with respect to a fermion field Grassmann-odd?
I unknowingly submitted plagarised work
Website returning plaintext password
Caught 2 students cheating together on the final exam that I proctored
Python program to find the most frequent letter in a text
Make 24 using exactly three 3s
Can a person survive on blood in place of water?
Alignment: "Breaking out" of environment (enumerate / minipage)
Python program to take in two strings and print the larger string
Can I tell a prospective employee that everyone in the team is leaving?
Could a 19.25mm revolver actually exist?
What are these arcade games in Ghostbusters 1984?
number headings
How should I introduce map drawing to my players?
Are these reasonable traits for someone with autism?
Why did the person in charge of a principality not just declare themself king?
Why did David Cameron offer a referendum on the European Union?
Count rotary dial pulses in a phone number (including letters)
Inconsistent results from Wolfram Could
Using credit/debit card details vs swiping a card in a payment (credit card) terminal
What are the real benefits of using Salesforce DX?
Where's this lookout in Nova Scotia?
Why does Excel inconsistently manage the cut rows range during a cut and paste to a new sheet?
Cutting Row with Data and moving to different sheet VBAExcel VBA remove blank row after cutCut + Insert sometimes deletes and shifts cells, other times it doesn't. Why?In Excel VBA, how can I test if an Excel.Range object variable loses its reference without raising runtime error 424..?Cut Paste data from excel sheet using Autofilter in Excel VBAWeird Excel bug when deleting sheet with named ranges?Cut and Paste Columns in Excel Range with c#Search for text in a string, copy & paste rows to new sheetVBA Macro code fail; Copying and Pasting from range into new rangecopy data from only 3 sheets and paste it in new sheet - ExcelLoop through excel spreadsheets in different workbooksIf cell = closed project, cut cell and past into “Projects closed” worksheet on next available rowExcel VBA: Trigger macro on cut/paste/delete/insert eventsExcel VBA - Routine that finds on one sheet and paste on another moving destination cells down
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have identified an inconsistency in the source range address for cut cells.
I have a utility in an Excel-based tool that migrates rows that the user selects. The user then must select the destination, which can be on the same sheet or on a different sheet.
The issue of Excel leaving blank lines when cutting and inserting into a different sheet are well known (c.f. Cutting Row with Data and moving to different sheet VBA and Excel VBA remove blank row after cut) and they have solutions for addressing it. However, my tool uses a different approach (similar to https://stackoverflow.com/a/27093382/9101981) - which I will now show does not work as intended.
Sub TestMigration()
'' Source Cells = migrateSource.Range("A3:A4") ' this simulates how the ranges are selected in the real world application
'' Target Cells = migrateTarget.Range("A7") ' this simulates the real world application where this is chosen through a pick box
MigrateRows migrateSource.Range("A3:A4"), migrateTarget.Range("A7")
End Sub
Sub MigrateRows(sourceCells As Range, targetCells As Range)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.EntireRow.Delete
End Sub
The source and target data is:
The expected results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$3:$A$4
The actual results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
The data above shows that the source range has taken on address of the inserted cells, but not the worksheet name. If the cut cells address were to "move", I would expect it to also take on the new worksheet.
Additional information - what else did I try doing this?
- I tried to create a copy of the source cells. As this is copying a reference, I did not expect it to work, and was not disappointed.
Code:
Sub MigrateRows1(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Set tSourceRowsCopy = sourceCells
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.EntireRow.Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
'Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 1 (MigrateRows1)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried to create a new reference based on the address, but created this reference before the move.
Code:
Sub MigrateRows2(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Set tSourceRowsCopy = sourceCells.Worksheet.Range(tSourceAddress)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.Delete
' Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
' Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 2 (MigrateRows2)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried saving the address, and then creating a new reference just before the delete. This worked.
Code:
Sub MigrateRows3(sourceCells As Range, targetCells As Range)
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "Address to be deleted: " & sourceCells.Worksheet.Name & "," & tSourceAddress
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.Worksheet.Range(tSourceAddress).Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
End Sub
excel vba
asked Mar 24 at 4:12
AJDAJD
1,5772514
add a comment |
I have identified an inconsistency in the source range address for cut cells.
I have a utility in an Excel-based tool that migrates rows that the user selects. The user then must select the destination, which can be on the same sheet or on a different sheet.
The issue of Excel leaving blank lines when cutting and inserting into a different sheet are well known (c.f. Cutting Row with Data and moving to different sheet VBA and Excel VBA remove blank row after cut) and they have solutions for addressing it. However, my tool uses a different approach (similar to https://stackoverflow.com/a/27093382/9101981) - which I will now show does not work as intended.
Sub TestMigration()
'' Source Cells = migrateSource.Range("A3:A4") ' this simulates how the ranges are selected in the real world application
'' Target Cells = migrateTarget.Range("A7") ' this simulates the real world application where this is chosen through a pick box
MigrateRows migrateSource.Range("A3:A4"), migrateTarget.Range("A7")
End Sub
Sub MigrateRows(sourceCells As Range, targetCells As Range)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.EntireRow.Delete
End Sub
The source and target data is:
The expected results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$3:$A$4
The actual results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
The data above shows that the source range has taken on address of the inserted cells, but not the worksheet name. If the cut cells address were to "move", I would expect it to also take on the new worksheet.
Additional information - what else did I try doing this?
- I tried to create a copy of the source cells. As this is copying a reference, I did not expect it to work, and was not disappointed.
Code:
Sub MigrateRows1(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Set tSourceRowsCopy = sourceCells
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.EntireRow.Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
'Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 1 (MigrateRows1)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried to create a new reference based on the address, but created this reference before the move.
Code:
Sub MigrateRows2(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Set tSourceRowsCopy = sourceCells.Worksheet.Range(tSourceAddress)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.Delete
' Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
' Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 2 (MigrateRows2)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried saving the address, and then creating a new reference just before the delete. This worked.
Code:
Sub MigrateRows3(sourceCells As Range, targetCells As Range)
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "Address to be deleted: " & sourceCells.Worksheet.Name & "," & tSourceAddress
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.Worksheet.Range(tSourceAddress).Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
End Sub
excel vba
asked Mar 24 at 4:12
AJDAJD
1,5772514
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53
add a comment |
I have identified an inconsistency in the source range address for cut cells.
I have a utility in an Excel-based tool that migrates rows that the user selects. The user then must select the destination, which can be on the same sheet or on a different sheet.
The issue of Excel leaving blank lines when cutting and inserting into a different sheet are well known (c.f. Cutting Row with Data and moving to different sheet VBA and Excel VBA remove blank row after cut) and they have solutions for addressing it. However, my tool uses a different approach (similar to https://stackoverflow.com/a/27093382/9101981) - which I will now show does not work as intended.
Sub TestMigration()
'' Source Cells = migrateSource.Range("A3:A4") ' this simulates how the ranges are selected in the real world application
'' Target Cells = migrateTarget.Range("A7") ' this simulates the real world application where this is chosen through a pick box
MigrateRows migrateSource.Range("A3:A4"), migrateTarget.Range("A7")
End Sub
Sub MigrateRows(sourceCells As Range, targetCells As Range)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.EntireRow.Delete
End Sub
The source and target data is:
The expected results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$3:$A$4
The actual results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
The data above shows that the source range has taken on address of the inserted cells, but not the worksheet name. If the cut cells address were to "move", I would expect it to also take on the new worksheet.
Additional information - what else did I try doing this?
- I tried to create a copy of the source cells. As this is copying a reference, I did not expect it to work, and was not disappointed.
Code:
Sub MigrateRows1(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Set tSourceRowsCopy = sourceCells
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.EntireRow.Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
'Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 1 (MigrateRows1)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried to create a new reference based on the address, but created this reference before the move.
Code:
Sub MigrateRows2(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Set tSourceRowsCopy = sourceCells.Worksheet.Range(tSourceAddress)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.Delete
' Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
' Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 2 (MigrateRows2)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried saving the address, and then creating a new reference just before the delete. This worked.
Code:
Sub MigrateRows3(sourceCells As Range, targetCells As Range)
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "Address to be deleted: " & sourceCells.Worksheet.Name & "," & tSourceAddress
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.Worksheet.Range(tSourceAddress).Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
End Sub
excel vba
asked Mar 24 at 4:12
AJDAJD
1,5772514
I have identified an inconsistency in the source range address for cut cells.
I have a utility in an Excel-based tool that migrates rows that the user selects. The user then must select the destination, which can be on the same sheet or on a different sheet.
The issue of Excel leaving blank lines when cutting and inserting into a different sheet are well known (c.f. Cutting Row with Data and moving to different sheet VBA and Excel VBA remove blank row after cut) and they have solutions for addressing it. However, my tool uses a different approach (similar to https://stackoverflow.com/a/27093382/9101981) - which I will now show does not work as intended.
Sub TestMigration()
'' Source Cells = migrateSource.Range("A3:A4") ' this simulates how the ranges are selected in the real world application
'' Target Cells = migrateTarget.Range("A7") ' this simulates the real world application where this is chosen through a pick box
MigrateRows migrateSource.Range("A3:A4"), migrateTarget.Range("A7")
End Sub
Sub MigrateRows(sourceCells As Range, targetCells As Range)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.EntireRow.Delete
End Sub
The source and target data is:
The expected results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$3:$A$4
The actual results are:
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
The data above shows that the source range has taken on address of the inserted cells, but not the worksheet name. If the cut cells address were to "move", I would expect it to also take on the new worksheet.
Additional information - what else did I try doing this?
- I tried to create a copy of the source cells. As this is copying a reference, I did not expect it to work, and was not disappointed.
Code:
Sub MigrateRows1(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Set tSourceRowsCopy = sourceCells
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.EntireRow.Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
'Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 1 (MigrateRows1)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried to create a new reference based on the address, but created this reference before the move.
Code:
Sub MigrateRows2(sourceCells As Range, targetCells As Range)
Dim tSourceRowsCopy As Range
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Set tSourceRowsCopy = sourceCells.Worksheet.Range(tSourceAddress)
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After cut source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "After insert source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.Delete
' Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
' Debug.Print "After delete source_copy address: " & tSourceRowsCopy.Worksheet.Name & "," & tSourceRowsCopy.Address
End Sub
Test Run 2 (MigrateRows2)
Original source address: MigrateSource,$A$3:$A$4
After cut source address: MigrateSource,$A$3:$A$4
After cut source_copy address: MigrateSource,$A$3:$A$4
After insert source address: MigrateSource,$A$7:$A$8
After insert source_copy address: MigrateSource,$A$7:$A$8
Outcome: No change
- I tried saving the address, and then creating a new reference just before the delete. This worked.
Code:
Sub MigrateRows3(sourceCells As Range, targetCells As Range)
Dim tSourceAddress As String
tSourceAddress = sourceCells.Address
Debug.Print "Original source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
sourceCells.EntireRow.Cut ' the command used in the real world application
Debug.Print "After cut source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
targetCells.Insert
Debug.Print "After insert source address: " & sourceCells.Worksheet.Name & "," & sourceCells.Address
Debug.Print "Address to be deleted: " & sourceCells.Worksheet.Name & "," & tSourceAddress
If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.Worksheet.Range(tSourceAddress).Delete
'Debug.Print "After delete source address: " & tSourceRows.Worksheet.Name & "," & tSourceRows.Address
End Sub
excel vba
excel vba
asked Mar 24 at 4:12
AJDAJD
1,5772514
asked Mar 24 at 4:12
AJDAJD
1,5772514
edited Mar 24 at 4:33
AJD
asked Mar 24 at 4:12
AJDAJD
1,5772514
asked Mar 24 at 4:12
AJDAJD
1,5772514
asked Mar 24 at 4:12
AJDAJD
1,5772514
1,5772514
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53
add a comment |
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55320661%2fwhy-does-excel-inconsistently-manage-the-cut-rows-range-during-a-cut-and-paste-t%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55320661%2fwhy-does-excel-inconsistently-manage-the-cut-rows-range-during-a-cut-and-paste-t%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I was also interested in loosing named range reference and searched for good reading in vain. May refer my comment in SO link1 also link2 terming it as expected excel behavior.
– Ahmed AU
Mar 24 at 7:26
Not quite the same. As you can see, I am not trying to reference the deleted range (which rightly gives an error) - this is about an inconsistency in a range that is moved.
– AJD
Mar 24 at 18:53