When pasting, how can I ignore hidden columns in the destination range?
2
I have two worksheets s1 and s2.
Data on s1 looks like:
+---+-----------+--------+------+
| | A | B | C |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
On s2, column B is hidden, like this:
+---+---+---+---+
| | A | C | D |
+---+---+---+---+
| 1 | | | |
+---+---+---+---+
When I try to copy 's1'!A1:C1 to 's2'!A1:D1, I get this:
+---+-----------+------+---+
| | A | C | D |
+---+-----------+------+---+
| 1 | scarecrow | lion | |
+---+-----------+------+---+
But I want the paste to ignore the hidden column B and give me this:
+---+-----------+--------+------+
| | A | C | D |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
How can I make the paste only apply to visible cells?
microsoft-excel microsoft-excel-2010
asked Apr 7 '14 at 18:22
sigilsigil
2852822
|
show 3 more comments
2
I have two worksheets s1 and s2.
Data on s1 looks like:
+---+-----------+--------+------+
| | A | B | C |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
On s2, column B is hidden, like this:
+---+---+---+---+
| | A | C | D |
+---+---+---+---+
| 1 | | | |
+---+---+---+---+
When I try to copy 's1'!A1:C1 to 's2'!A1:D1, I get this:
+---+-----------+------+---+
| | A | C | D |
+---+-----------+------+---+
| 1 | scarecrow | lion | |
+---+-----------+------+---+
But I want the paste to ignore the hidden column B and give me this:
+---+-----------+--------+------+
| | A | C | D |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
How can I make the paste only apply to visible cells?
microsoft-excel microsoft-excel-2010
asked Apr 7 '14 at 18:22
sigilsigil
2852822
2
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
1
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22
|
show 3 more comments
2
2
2
I have two worksheets s1 and s2.
Data on s1 looks like:
+---+-----------+--------+------+
| | A | B | C |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
On s2, column B is hidden, like this:
+---+---+---+---+
| | A | C | D |
+---+---+---+---+
| 1 | | | |
+---+---+---+---+
When I try to copy 's1'!A1:C1 to 's2'!A1:D1, I get this:
+---+-----------+------+---+
| | A | C | D |
+---+-----------+------+---+
| 1 | scarecrow | lion | |
+---+-----------+------+---+
But I want the paste to ignore the hidden column B and give me this:
+---+-----------+--------+------+
| | A | C | D |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
How can I make the paste only apply to visible cells?
microsoft-excel microsoft-excel-2010
asked Apr 7 '14 at 18:22
sigilsigil
2852822
I have two worksheets s1 and s2.
Data on s1 looks like:
+---+-----------+--------+------+
| | A | B | C |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
On s2, column B is hidden, like this:
+---+---+---+---+
| | A | C | D |
+---+---+---+---+
| 1 | | | |
+---+---+---+---+
When I try to copy 's1'!A1:C1 to 's2'!A1:D1, I get this:
+---+-----------+------+---+
| | A | C | D |
+---+-----------+------+---+
| 1 | scarecrow | lion | |
+---+-----------+------+---+
But I want the paste to ignore the hidden column B and give me this:
+---+-----------+--------+------+
| | A | C | D |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+
How can I make the paste only apply to visible cells?
microsoft-excel microsoft-excel-2010
microsoft-excel microsoft-excel-2010
asked Apr 7 '14 at 18:22
sigilsigil
2852822
asked Apr 7 '14 at 18:22
sigilsigil
2852822
asked Apr 7 '14 at 18:22
sigilsigil
2852822
asked Apr 7 '14 at 18:22
sigilsigil
2852822
asked Apr 7 '14 at 18:22
sigilsigil
2852822
2852822
2
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
1
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22
|
show 3 more comments
2
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
1
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22
2
2
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
1
1
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22
|
show 3 more comments
2 Answers
2
active
oldest
votes
0
You can select "visible only" and then paste.
- Select range of cells.
- Find & select dialog -> Go to special
- Select "visible only" - now you selected only visible cells.
- All you have to do now is copy & paste.
More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx
answered Apr 8 '14 at 14:03
lowaklowak
1338
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
add a comment |
0
To Paste data and skip hidden rows at
destination do the followings:
- Select the Destination Range.
- Press
F5then selectSpecialthen
Visible Cells Only. - Press
=and click on the first cell
at your Source Range. - Press 'Ctrl + Enter.'
Note, this should bring in data from the visible rows provided their position is identical to the destination range.
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
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
});
}
});
draft saved
draft discarded
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%2f738823%2fwhen-pasting-how-can-i-ignore-hidden-columns-in-the-destination-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can select "visible only" and then paste.
- Select range of cells.
- Find & select dialog -> Go to special
- Select "visible only" - now you selected only visible cells.
- All you have to do now is copy & paste.
More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx
answered Apr 8 '14 at 14:03
lowaklowak
1338
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
add a comment |
0
You can select "visible only" and then paste.
- Select range of cells.
- Find & select dialog -> Go to special
- Select "visible only" - now you selected only visible cells.
- All you have to do now is copy & paste.
More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx
answered Apr 8 '14 at 14:03
lowaklowak
1338
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
add a comment |
0
0
0
You can select "visible only" and then paste.
- Select range of cells.
- Find & select dialog -> Go to special
- Select "visible only" - now you selected only visible cells.
- All you have to do now is copy & paste.
More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx
answered Apr 8 '14 at 14:03
lowaklowak
1338
You can select "visible only" and then paste.
- Select range of cells.
- Find & select dialog -> Go to special
- Select "visible only" - now you selected only visible cells.
- All you have to do now is copy & paste.
More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx
answered Apr 8 '14 at 14:03
lowaklowak
1338
answered Apr 8 '14 at 14:03
lowaklowak
1338
answered Apr 8 '14 at 14:03
lowaklowak
1338
answered Apr 8 '14 at 14:03
lowaklowak
1338
1338
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
add a comment |
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
1
1
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range.
– sigil
Apr 8 '14 at 15:32
add a comment |
0
To Paste data and skip hidden rows at
destination do the followings:
- Select the Destination Range.
- Press
F5then selectSpecialthen
Visible Cells Only. - Press
=and click on the first cell
at your Source Range. - Press 'Ctrl + Enter.'
Note, this should bring in data from the visible rows provided their position is identical to the destination range.
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
add a comment |
0
To Paste data and skip hidden rows at
destination do the followings:
- Select the Destination Range.
- Press
F5then selectSpecialthen
Visible Cells Only. - Press
=and click on the first cell
at your Source Range. - Press 'Ctrl + Enter.'
Note, this should bring in data from the visible rows provided their position is identical to the destination range.
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
add a comment |
0
0
0
To Paste data and skip hidden rows at
destination do the followings:
- Select the Destination Range.
- Press
F5then selectSpecialthen
Visible Cells Only. - Press
=and click on the first cell
at your Source Range. - Press 'Ctrl + Enter.'
Note, this should bring in data from the visible rows provided their position is identical to the destination range.
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
To Paste data and skip hidden rows at
destination do the followings:
- Select the Destination Range.
- Press
F5then selectSpecialthen
Visible Cells Only. - Press
=and click on the first cell
at your Source Range. - Press 'Ctrl + Enter.'
Note, this should bring in data from the visible rows provided their position is identical to the destination range.
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
edited Sep 5 '18 at 10:34
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
answered Sep 5 '18 at 10:28
Rajesh SRajesh S
1
1
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f738823%2fwhen-pasting-how-can-i-ignore-hidden-columns-in-the-destination-range%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
No, there isn't an easy way to do this without VBA
– Raystafarian
Apr 7 '14 at 18:46
This would seem like a common enough operation, once you start making use of hidden columns and rows.
– Dane
Apr 7 '14 at 19:44
You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question?
– Dane
Apr 7 '14 at 21:17
@Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language.
– sigil
Apr 7 '14 at 21:23
1
In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk
– Dane
Apr 8 '14 at 18:22