Can't see named range for table when worksheet is hidden
I'm trying to access a named range via VBA:
Set rng = selectionSht.Parent.Names(rangeName).RefersToRange
However, this particular named range is mapped to a table on a hidden worksheet. When the worksheet is hidden, the named range disappears from the list of named ranges shown when I go to Formulas->Name Manager, but it reappears in the list when I unhide the sheet. This does not happen to other named ranges that refer to static ranges--it seems to only be an issue for named ranges that correspond to tables.
When the sheet is hidden, the above line of VBA code returns:
Run-time error '1004':
Application-defined or object-defined error
How can I make this named range readable when the sheet is hidden?
microsoft-excel microsoft-excel-2010 vba
asked Jan 29 at 20:23
sigilsigil
2953822
add a comment |
I'm trying to access a named range via VBA:
Set rng = selectionSht.Parent.Names(rangeName).RefersToRange
However, this particular named range is mapped to a table on a hidden worksheet. When the worksheet is hidden, the named range disappears from the list of named ranges shown when I go to Formulas->Name Manager, but it reappears in the list when I unhide the sheet. This does not happen to other named ranges that refer to static ranges--it seems to only be an issue for named ranges that correspond to tables.
When the sheet is hidden, the above line of VBA code returns:
Run-time error '1004':
Application-defined or object-defined error
How can I make this named range readable when the sheet is hidden?
microsoft-excel microsoft-excel-2010 vba
asked Jan 29 at 20:23
sigilsigil
2953822
2
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
1
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26
add a comment |
I'm trying to access a named range via VBA:
Set rng = selectionSht.Parent.Names(rangeName).RefersToRange
However, this particular named range is mapped to a table on a hidden worksheet. When the worksheet is hidden, the named range disappears from the list of named ranges shown when I go to Formulas->Name Manager, but it reappears in the list when I unhide the sheet. This does not happen to other named ranges that refer to static ranges--it seems to only be an issue for named ranges that correspond to tables.
When the sheet is hidden, the above line of VBA code returns:
Run-time error '1004':
Application-defined or object-defined error
How can I make this named range readable when the sheet is hidden?
microsoft-excel microsoft-excel-2010 vba
asked Jan 29 at 20:23
sigilsigil
2953822
I'm trying to access a named range via VBA:
Set rng = selectionSht.Parent.Names(rangeName).RefersToRange
However, this particular named range is mapped to a table on a hidden worksheet. When the worksheet is hidden, the named range disappears from the list of named ranges shown when I go to Formulas->Name Manager, but it reappears in the list when I unhide the sheet. This does not happen to other named ranges that refer to static ranges--it seems to only be an issue for named ranges that correspond to tables.
When the sheet is hidden, the above line of VBA code returns:
Run-time error '1004':
Application-defined or object-defined error
How can I make this named range readable when the sheet is hidden?
microsoft-excel microsoft-excel-2010 vba
microsoft-excel microsoft-excel-2010 vba
asked Jan 29 at 20:23
sigilsigil
2953822
asked Jan 29 at 20:23
sigilsigil
2953822
asked Jan 29 at 20:23
sigilsigil
2953822
asked Jan 29 at 20:23
sigilsigil
2953822
asked Jan 29 at 20:23
sigilsigil
2953822
2953822
2
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
1
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26
add a comment |
2
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
1
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26
2
2
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
1
1
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26
add a comment |
1 Answer
1
active
oldest
votes
You can't directly do what you want. In VBA, if a cell is not visible to the user, then VBA also can't see it. For example if you set a cell's width to 0, VBA treats that as not visible and therefore you can't access it, similar to what you have above.
The way you can achieve a task while keeping a cell / row / column hidden is to do the following:
- Turn
Application.ScreenUpdating = False
.Showthe hidden area that you are referrencing- Perform whatever operation you need to do to the given cells
.Hidethe area that you worked on- Turn
Application.ScreenUpdating = Trueback on.
From the user's standpoint since screenupdating was off, the cell / row / column never was shown but you can still achieve your task.
answered Jan 30 at 13:54
Eric FEric F
2,80031331
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1399798%2fcant-see-named-range-for-table-when-worksheet-is-hidden%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
You can't directly do what you want. In VBA, if a cell is not visible to the user, then VBA also can't see it. For example if you set a cell's width to 0, VBA treats that as not visible and therefore you can't access it, similar to what you have above.
The way you can achieve a task while keeping a cell / row / column hidden is to do the following:
- Turn
Application.ScreenUpdating = False
.Showthe hidden area that you are referrencing- Perform whatever operation you need to do to the given cells
.Hidethe area that you worked on- Turn
Application.ScreenUpdating = Trueback on.
From the user's standpoint since screenupdating was off, the cell / row / column never was shown but you can still achieve your task.
answered Jan 30 at 13:54
Eric FEric F
2,80031331
add a comment |
You can't directly do what you want. In VBA, if a cell is not visible to the user, then VBA also can't see it. For example if you set a cell's width to 0, VBA treats that as not visible and therefore you can't access it, similar to what you have above.
The way you can achieve a task while keeping a cell / row / column hidden is to do the following:
- Turn
Application.ScreenUpdating = False
.Showthe hidden area that you are referrencing- Perform whatever operation you need to do to the given cells
.Hidethe area that you worked on- Turn
Application.ScreenUpdating = Trueback on.
From the user's standpoint since screenupdating was off, the cell / row / column never was shown but you can still achieve your task.
answered Jan 30 at 13:54
Eric FEric F
2,80031331
add a comment |
You can't directly do what you want. In VBA, if a cell is not visible to the user, then VBA also can't see it. For example if you set a cell's width to 0, VBA treats that as not visible and therefore you can't access it, similar to what you have above.
The way you can achieve a task while keeping a cell / row / column hidden is to do the following:
- Turn
Application.ScreenUpdating = False
.Showthe hidden area that you are referrencing- Perform whatever operation you need to do to the given cells
.Hidethe area that you worked on- Turn
Application.ScreenUpdating = Trueback on.
From the user's standpoint since screenupdating was off, the cell / row / column never was shown but you can still achieve your task.
answered Jan 30 at 13:54
Eric FEric F
2,80031331
You can't directly do what you want. In VBA, if a cell is not visible to the user, then VBA also can't see it. For example if you set a cell's width to 0, VBA treats that as not visible and therefore you can't access it, similar to what you have above.
The way you can achieve a task while keeping a cell / row / column hidden is to do the following:
- Turn
Application.ScreenUpdating = False
.Showthe hidden area that you are referrencing- Perform whatever operation you need to do to the given cells
.Hidethe area that you worked on- Turn
Application.ScreenUpdating = Trueback on.
From the user's standpoint since screenupdating was off, the cell / row / column never was shown but you can still achieve your task.
answered Jan 30 at 13:54
Eric FEric F
2,80031331
answered Jan 30 at 13:54
Eric FEric F
2,80031331
answered Jan 30 at 13:54
Eric FEric F
2,80031331
answered Jan 30 at 13:54
Eric FEric F
2,80031331
2,80031331
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1399798%2fcant-see-named-range-for-table-when-worksheet-is-hidden%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
2
As far as I know, I don't think you can. Some VBA functions only work on what is visible. I.E. if you had a cell with width set to 0, it treats it as hidden and as something you can't access. What you may want to do is to put screen updating = false, show the hidden worksheet, do what you need to with the named range, make the worksheet hidden again, and then turn on screen updating = true. From the perspective of the user, they still never see the hidden worksheet
– Eric F
Jan 29 at 20:32
1
@EricF, that sounds like an answer.
– fixer1234
Jan 29 at 22:26