Store `.Range` object entirely in memory?
Is there a way to store .Range object entirely in memory?
I want to store ranges as dictionary values and then be able to unpack the ranges somewhere else. But before unpacking, the original ranges will be modified.
This is the code I have for storing the ranges in dictionary:
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
It will do the job only if I don't modify the ranges between creating the dict and unpacking, because while unpacking the VBA still reads the ranges from sheet, not from memory (dictionary):
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
'modify (clear)
For Each rw In rng.Rows
rw.Clear
Next rw
'now this will paste only empty fields
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
I could achieve what I want through storing each range cells value in an array or collection, and then unpack them, but is there a less wordy way?
excel vba
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
add a comment |
Is there a way to store .Range object entirely in memory?
I want to store ranges as dictionary values and then be able to unpack the ranges somewhere else. But before unpacking, the original ranges will be modified.
This is the code I have for storing the ranges in dictionary:
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
It will do the job only if I don't modify the ranges between creating the dict and unpacking, because while unpacking the VBA still reads the ranges from sheet, not from memory (dictionary):
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
'modify (clear)
For Each rw In rng.Rows
rw.Clear
Next rw
'now this will paste only empty fields
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
I could achieve what I want through storing each range cells value in an array or collection, and then unpack them, but is there a less wordy way?
excel vba
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
1
ARangeobject is nothing more than a "view window" to a range of cells on a sheet. You cannot have aRangethat is disconnected from cells and has its ownValue.
– GSerg
Nov 15 '18 at 9:42
add a comment |
Is there a way to store .Range object entirely in memory?
I want to store ranges as dictionary values and then be able to unpack the ranges somewhere else. But before unpacking, the original ranges will be modified.
This is the code I have for storing the ranges in dictionary:
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
It will do the job only if I don't modify the ranges between creating the dict and unpacking, because while unpacking the VBA still reads the ranges from sheet, not from memory (dictionary):
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
'modify (clear)
For Each rw In rng.Rows
rw.Clear
Next rw
'now this will paste only empty fields
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
I could achieve what I want through storing each range cells value in an array or collection, and then unpack them, but is there a less wordy way?
excel vba
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
Is there a way to store .Range object entirely in memory?
I want to store ranges as dictionary values and then be able to unpack the ranges somewhere else. But before unpacking, the original ranges will be modified.
This is the code I have for storing the ranges in dictionary:
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
It will do the job only if I don't modify the ranges between creating the dict and unpacking, because while unpacking the VBA still reads the ranges from sheet, not from memory (dictionary):
Sub range_to_dict()
Application.EnableEvents = False
Dim d As New scripting.Dictionary
Dim rng As Range
Dim rw As Range
Dim paste_loc As Range
Set rng = Range("A1:B13")
Set paste_loc = rng.Offset(rng.Rows.Count + 2, 0).Resize(1, 1)
For Each rw In rng.Rows
d.Add (rw.Cells(1).Value), rw
Next rw
'modify (clear)
For Each rw In rng.Rows
rw.Clear
Next rw
'now this will paste only empty fields
For Each k In d.Keys
d(k).Copy
paste_loc.PasteSpecial xlPasteValues
Set paste_loc = paste_loc.Offset(1, 0)
Next k
Application.EnableEvents = True
End Sub
I could achieve what I want through storing each range cells value in an array or collection, and then unpack them, but is there a less wordy way?
excel vba
excel vba
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
edited Nov 15 '18 at 9:39
Pᴇʜ
23.6k62951
23.6k62951
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
asked Nov 15 '18 at 9:37
barciewiczbarciewicz
672313
672313
1
ARangeobject is nothing more than a "view window" to a range of cells on a sheet. You cannot have aRangethat is disconnected from cells and has its ownValue.
– GSerg
Nov 15 '18 at 9:42
add a comment |
1
ARangeobject is nothing more than a "view window" to a range of cells on a sheet. You cannot have aRangethat is disconnected from cells and has its ownValue.
– GSerg
Nov 15 '18 at 9:42
1
1
A
Range object is nothing more than a "view window" to a range of cells on a sheet. You cannot have a Range that is disconnected from cells and has its own Value.– GSerg
Nov 15 '18 at 9:42
A
Range object is nothing more than a "view window" to a range of cells on a sheet. You cannot have a Range that is disconnected from cells and has its own Value.– GSerg
Nov 15 '18 at 9:42
add a comment |
1 Answer
1
active
oldest
votes
It is not possible to store a Range in a variable (memory). It is always just a reference to the original range that is stored in the variable.
But you can read the values of the range into an array:
Dim MyArr As Variant
MyArr = Range("A1:A10").Value
If you now modify your range A1:A10 the array will still contain the old values.
And of course you can write them back into cells
Range("A1:A10").Value = MyArr
note that when writing back, the range must be the same size as the array. Otherwise some values will be missing.
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.Range("A1:A10")is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.
– Pᴇʜ
Nov 15 '18 at 10:46
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
add a comment |
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%2f53316387%2fstore-range-object-entirely-in-memory%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is not possible to store a Range in a variable (memory). It is always just a reference to the original range that is stored in the variable.
But you can read the values of the range into an array:
Dim MyArr As Variant
MyArr = Range("A1:A10").Value
If you now modify your range A1:A10 the array will still contain the old values.
And of course you can write them back into cells
Range("A1:A10").Value = MyArr
note that when writing back, the range must be the same size as the array. Otherwise some values will be missing.
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.Range("A1:A10")is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.
– Pᴇʜ
Nov 15 '18 at 10:46
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
add a comment |
It is not possible to store a Range in a variable (memory). It is always just a reference to the original range that is stored in the variable.
But you can read the values of the range into an array:
Dim MyArr As Variant
MyArr = Range("A1:A10").Value
If you now modify your range A1:A10 the array will still contain the old values.
And of course you can write them back into cells
Range("A1:A10").Value = MyArr
note that when writing back, the range must be the same size as the array. Otherwise some values will be missing.
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.Range("A1:A10")is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.
– Pᴇʜ
Nov 15 '18 at 10:46
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
add a comment |
It is not possible to store a Range in a variable (memory). It is always just a reference to the original range that is stored in the variable.
But you can read the values of the range into an array:
Dim MyArr As Variant
MyArr = Range("A1:A10").Value
If you now modify your range A1:A10 the array will still contain the old values.
And of course you can write them back into cells
Range("A1:A10").Value = MyArr
note that when writing back, the range must be the same size as the array. Otherwise some values will be missing.
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
It is not possible to store a Range in a variable (memory). It is always just a reference to the original range that is stored in the variable.
But you can read the values of the range into an array:
Dim MyArr As Variant
MyArr = Range("A1:A10").Value
If you now modify your range A1:A10 the array will still contain the old values.
And of course you can write them back into cells
Range("A1:A10").Value = MyArr
note that when writing back, the range must be the same size as the array. Otherwise some values will be missing.
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
answered Nov 15 '18 at 9:42
PᴇʜPᴇʜ
23.6k62951
23.6k62951
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.Range("A1:A10")is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.
– Pᴇʜ
Nov 15 '18 at 10:46
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
add a comment |
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.Range("A1:A10")is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.
– Pᴇʜ
Nov 15 '18 at 10:46
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
Thanks. By "not possible" do you mean that such feature is just not implemented in VBA?
– barciewicz
Nov 15 '18 at 10:44
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.
Range("A1:A10") is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.– Pᴇʜ
Nov 15 '18 at 10:46
@barciewicz Yes, there is no possibility in VBA to have a range object that is not connected to cells in a worksheet.
Range("A1:A10") is only a reference that is pointing to the actual range in the worksheet, nothing more. There is no way to have a "virtual" range that is disconnected from a worksheet.– Pᴇʜ
Nov 15 '18 at 10:46
2
2
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
@barciewicz This feature is not implemented in Excel, not in VBA (you can use the Excel object model from any language that supports COM). I'm pretty certain it will never be implemented either.
– GSerg
Nov 15 '18 at 11:14
add a comment |
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%2f53316387%2fstore-range-object-entirely-in-memory%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
1
A
Rangeobject is nothing more than a "view window" to a range of cells on a sheet. You cannot have aRangethat is disconnected from cells and has its ownValue.– GSerg
Nov 15 '18 at 9:42