Mixing
Skipping copy of table when filter has no data
I have been building a large macro, and it uses tables to transfer some filtered data. When the table filters apply a filter and there is no positive results in the table the entire data set in the table is copied instead of nothing.
I have some ideas but they are starting to become complex and I have a series of tables using this method, so I am looking for a simpler way of avoiding a full data copy of values not intended to be copied.
ActiveSheet.ListObjects("iexp_period").Range.AutoFilter Field:=1, Criteria1 _
:=Array("Asset", "Asset(Rc)", "LVP", "LVP(Rc)"), Operator:=xlFilterValues
Range("iexp_period").Copy
So as stated if the table has no filtered results this copy copies the entire table's data even though all rows are hidden when copied.
Is there a quick way to avoid this please?
excel vba filter
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
add a comment |
I have been building a large macro, and it uses tables to transfer some filtered data. When the table filters apply a filter and there is no positive results in the table the entire data set in the table is copied instead of nothing.
I have some ideas but they are starting to become complex and I have a series of tables using this method, so I am looking for a simpler way of avoiding a full data copy of values not intended to be copied.
ActiveSheet.ListObjects("iexp_period").Range.AutoFilter Field:=1, Criteria1 _
:=Array("Asset", "Asset(Rc)", "LVP", "LVP(Rc)"), Operator:=xlFilterValues
Range("iexp_period").Copy
So as stated if the table has no filtered results this copy copies the entire table's data even though all rows are hidden when copied.
Is there a quick way to avoid this please?
excel vba filter
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41
add a comment |
I have been building a large macro, and it uses tables to transfer some filtered data. When the table filters apply a filter and there is no positive results in the table the entire data set in the table is copied instead of nothing.
I have some ideas but they are starting to become complex and I have a series of tables using this method, so I am looking for a simpler way of avoiding a full data copy of values not intended to be copied.
ActiveSheet.ListObjects("iexp_period").Range.AutoFilter Field:=1, Criteria1 _
:=Array("Asset", "Asset(Rc)", "LVP", "LVP(Rc)"), Operator:=xlFilterValues
Range("iexp_period").Copy
So as stated if the table has no filtered results this copy copies the entire table's data even though all rows are hidden when copied.
Is there a quick way to avoid this please?
excel vba filter
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
I have been building a large macro, and it uses tables to transfer some filtered data. When the table filters apply a filter and there is no positive results in the table the entire data set in the table is copied instead of nothing.
I have some ideas but they are starting to become complex and I have a series of tables using this method, so I am looking for a simpler way of avoiding a full data copy of values not intended to be copied.
ActiveSheet.ListObjects("iexp_period").Range.AutoFilter Field:=1, Criteria1 _
:=Array("Asset", "Asset(Rc)", "LVP", "LVP(Rc)"), Operator:=xlFilterValues
Range("iexp_period").Copy
So as stated if the table has no filtered results this copy copies the entire table's data even though all rows are hidden when copied.
Is there a quick way to avoid this please?
excel vba filter
excel vba filter
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
edited Nov 20 '18 at 18:09
CaptainMacro
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
asked Nov 20 '18 at 5:36
CaptainMacroCaptainMacro
62
62
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41
add a comment |
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41
add a comment |
1 Answer
1
active
oldest
votes
Iam not to sure since Iam an newbie to VBA myself but when i just researched a similar issue and this: http://www.vbaexpress.com/forum/showthread.php?40514-How-to-exclude-hidden-rows-columns-while-executing-a-VB-code helped me out a lot.
I assume that rows.hidden would be the method you are looking for? Maybe you need to negate it.
Here would be some additional example code (from allen wyatt not me!)
Sub NumberClients()
Dim c As Range
Dim j As Integer
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
j = 0
For Each c In Selection
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear
End If
Next c
End Sub
I hope this helps you a little :)
answered Nov 20 '18 at 9:24
G.MG.M
156
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%2f53386838%2fskipping-copy-of-table-when-filter-has-no-data%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
Iam not to sure since Iam an newbie to VBA myself but when i just researched a similar issue and this: http://www.vbaexpress.com/forum/showthread.php?40514-How-to-exclude-hidden-rows-columns-while-executing-a-VB-code helped me out a lot.
I assume that rows.hidden would be the method you are looking for? Maybe you need to negate it.
Here would be some additional example code (from allen wyatt not me!)
Sub NumberClients()
Dim c As Range
Dim j As Integer
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
j = 0
For Each c In Selection
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear
End If
Next c
End Sub
I hope this helps you a little :)
answered Nov 20 '18 at 9:24
G.MG.M
156
add a comment |
Iam not to sure since Iam an newbie to VBA myself but when i just researched a similar issue and this: http://www.vbaexpress.com/forum/showthread.php?40514-How-to-exclude-hidden-rows-columns-while-executing-a-VB-code helped me out a lot.
I assume that rows.hidden would be the method you are looking for? Maybe you need to negate it.
Here would be some additional example code (from allen wyatt not me!)
Sub NumberClients()
Dim c As Range
Dim j As Integer
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
j = 0
For Each c In Selection
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear
End If
Next c
End Sub
I hope this helps you a little :)
answered Nov 20 '18 at 9:24
G.MG.M
156
add a comment |
Iam not to sure since Iam an newbie to VBA myself but when i just researched a similar issue and this: http://www.vbaexpress.com/forum/showthread.php?40514-How-to-exclude-hidden-rows-columns-while-executing-a-VB-code helped me out a lot.
I assume that rows.hidden would be the method you are looking for? Maybe you need to negate it.
Here would be some additional example code (from allen wyatt not me!)
Sub NumberClients()
Dim c As Range
Dim j As Integer
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
j = 0
For Each c In Selection
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear
End If
Next c
End Sub
I hope this helps you a little :)
answered Nov 20 '18 at 9:24
G.MG.M
156
Iam not to sure since Iam an newbie to VBA myself but when i just researched a similar issue and this: http://www.vbaexpress.com/forum/showthread.php?40514-How-to-exclude-hidden-rows-columns-while-executing-a-VB-code helped me out a lot.
I assume that rows.hidden would be the method you are looking for? Maybe you need to negate it.
Here would be some additional example code (from allen wyatt not me!)
Sub NumberClients()
Dim c As Range
Dim j As Integer
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
j = 0
For Each c In Selection
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear
End If
Next c
End Sub
I hope this helps you a little :)
answered Nov 20 '18 at 9:24
G.MG.M
156
answered Nov 20 '18 at 9:24
G.MG.M
156
answered Nov 20 '18 at 9:24
G.MG.M
156
answered Nov 20 '18 at 9:24
G.MG.M
156
156
add a comment |
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%2f53386838%2fskipping-copy-of-table-when-filter-has-no-data%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
What software package is this macro for? What are you asking about? It is not clear.
– Brian Tompsett - 汤莱恩
Nov 20 '18 at 7:49
you can check the data range height stackoverflow.com/questions/48341102/…
– Slai
Nov 20 '18 at 10:11
Thanks Slai! One small line of code is the solution, within a with statement. If .Height Then .Copy Else Exit Sub
– CaptainMacro
Nov 20 '18 at 22:41