Failed selection on union range
0
I try to select the range that was union under one variable, but it fails.
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
selectedRows.Select '<------ fail here
Copy Worksheets("main").Range("A3")
End Sub
The macro failed and error message said: Select method or Range class failed.
What is wrong with my code?
excel vba excel-vba
edited Nov 21 at 8:43
EvR
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
add a comment |
0
I try to select the range that was union under one variable, but it fails.
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
selectedRows.Select '<------ fail here
Copy Worksheets("main").Range("A3")
End Sub
The macro failed and error message said: Select method or Range class failed.
What is wrong with my code?
excel vba excel-vba
edited Nov 21 at 8:43
EvR
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
You should test ifselectedRows = Nothingbefore Selecting. Also, isWorksheets("contacts")hidden by any chance? One more thought try starting withSet selectedRows = Worksheets("contacts").Range("A1")as you testB1in your loop and not sure if you can have it in your selected range twice.
– Tom
Nov 21 at 9:16
Without too much testing the code worked for me - except that last line which should beWorksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.
– Darren Bartrup-Cook
Nov 21 at 9:43
add a comment |
0
0
0
I try to select the range that was union under one variable, but it fails.
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
selectedRows.Select '<------ fail here
Copy Worksheets("main").Range("A3")
End Sub
The macro failed and error message said: Select method or Range class failed.
What is wrong with my code?
excel vba excel-vba
edited Nov 21 at 8:43
EvR
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
I try to select the range that was union under one variable, but it fails.
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
selectedRows.Select '<------ fail here
Copy Worksheets("main").Range("A3")
End Sub
The macro failed and error message said: Select method or Range class failed.
What is wrong with my code?
excel vba excel-vba
excel vba excel-vba
edited Nov 21 at 8:43
EvR
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
edited Nov 21 at 8:43
EvR
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
edited Nov 21 at 8:43
EvR
1,2342313
edited Nov 21 at 8:43
EvR
1,2342313
edited Nov 21 at 8:43
EvR
1,2342313
1,2342313
asked Nov 21 at 7:57
Ashraf Misran
217
asked Nov 21 at 7:57
Ashraf Misran
217
asked Nov 21 at 7:57
Ashraf Misran
217
217
You should test ifselectedRows = Nothingbefore Selecting. Also, isWorksheets("contacts")hidden by any chance? One more thought try starting withSet selectedRows = Worksheets("contacts").Range("A1")as you testB1in your loop and not sure if you can have it in your selected range twice.
– Tom
Nov 21 at 9:16
Without too much testing the code worked for me - except that last line which should beWorksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.
– Darren Bartrup-Cook
Nov 21 at 9:43
add a comment |
You should test ifselectedRows = Nothingbefore Selecting. Also, isWorksheets("contacts")hidden by any chance? One more thought try starting withSet selectedRows = Worksheets("contacts").Range("A1")as you testB1in your loop and not sure if you can have it in your selected range twice.
– Tom
Nov 21 at 9:16
Without too much testing the code worked for me - except that last line which should beWorksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.
– Darren Bartrup-Cook
Nov 21 at 9:43
You should test if
selectedRows = Nothing before Selecting. Also, is Worksheets("contacts") hidden by any chance? One more thought try starting with Set selectedRows = Worksheets("contacts").Range("A1") as you test B1 in your loop and not sure if you can have it in your selected range twice.– Tom
Nov 21 at 9:16
You should test if
selectedRows = Nothing before Selecting. Also, is Worksheets("contacts") hidden by any chance? One more thought try starting with Set selectedRows = Worksheets("contacts").Range("A1") as you test B1 in your loop and not sure if you can have it in your selected range twice.– Tom
Nov 21 at 9:16
Without too much testing the code worked for me - except that last line which should be
Worksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.– Darren Bartrup-Cook
Nov 21 at 9:43
Without too much testing the code worked for me - except that last line which should be
Worksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.– Darren Bartrup-Cook
Nov 21 at 9:43
add a comment |
1 Answer
1
active
oldest
votes
1
You get the error msg because sheet "contacts" is not active. Add this line:
Worksheets("contacts").Activate
Like this:
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
Worksheets("contacts").Activate
selectedRows.Select '<------ fail here
'this line below is incorrect
'Copy Worksheets("main").Range("A3")
End Sub
I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.
edited Nov 21 at 11:10
JohnyL
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3")should beWorksheets("main").Range("A3").Copy
– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it beselectedRows.Copy Worksheets("main").Range("A3")?
– SJR
Nov 21 at 12:24
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53407496%2ffailed-selection-on-union-range%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
1
You get the error msg because sheet "contacts" is not active. Add this line:
Worksheets("contacts").Activate
Like this:
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
Worksheets("contacts").Activate
selectedRows.Select '<------ fail here
'this line below is incorrect
'Copy Worksheets("main").Range("A3")
End Sub
I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.
edited Nov 21 at 11:10
JohnyL
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3")should beWorksheets("main").Range("A3").Copy
– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it beselectedRows.Copy Worksheets("main").Range("A3")?
– SJR
Nov 21 at 12:24
add a comment |
1
You get the error msg because sheet "contacts" is not active. Add this line:
Worksheets("contacts").Activate
Like this:
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
Worksheets("contacts").Activate
selectedRows.Select '<------ fail here
'this line below is incorrect
'Copy Worksheets("main").Range("A3")
End Sub
I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.
edited Nov 21 at 11:10
JohnyL
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3")should beWorksheets("main").Range("A3").Copy
– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it beselectedRows.Copy Worksheets("main").Range("A3")?
– SJR
Nov 21 at 12:24
add a comment |
1
1
1
You get the error msg because sheet "contacts" is not active. Add this line:
Worksheets("contacts").Activate
Like this:
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
Worksheets("contacts").Activate
selectedRows.Select '<------ fail here
'this line below is incorrect
'Copy Worksheets("main").Range("A3")
End Sub
I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.
edited Nov 21 at 11:10
JohnyL
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
You get the error msg because sheet "contacts" is not active. Add this line:
Worksheets("contacts").Activate
Like this:
Sub filter()
'Clear prev result table
Worksheets("main").Range("A3").CurrentRegion.Delete
'Declare variable
Dim selectedRows As Range
Set selectedRows = Worksheets("contacts").Range("A1:B1")
'Get total used range in contacts database
numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
If cell.Value = Worksheets("main").Range("B1").Value Then
'If true, push into array called selectedRows
Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
End If
Next
Worksheets("contacts").Activate
selectedRows.Select '<------ fail here
'this line below is incorrect
'Copy Worksheets("main").Range("A3")
End Sub
I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.
edited Nov 21 at 11:10
JohnyL
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
edited Nov 21 at 11:10
JohnyL
3,4021822
edited Nov 21 at 11:10
JohnyL
3,4021822
edited Nov 21 at 11:10
JohnyL
3,4021822
3,4021822
answered Nov 21 at 8:09
Pawel Czyz
6522517
answered Nov 21 at 8:09
Pawel Czyz
6522517
answered Nov 21 at 8:09
Pawel Czyz
6522517
6522517
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3")should beWorksheets("main").Range("A3").Copy
– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it beselectedRows.Copy Worksheets("main").Range("A3")?
– SJR
Nov 21 at 12:24
add a comment |
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3")should beWorksheets("main").Range("A3").Copy
– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it beselectedRows.Copy Worksheets("main").Range("A3")?
– SJR
Nov 21 at 12:24
still not succesful
– Ashraf Misran
Nov 21 at 9:03
still not succesful
– Ashraf Misran
Nov 21 at 9:03
Copy Worksheets("main").Range("A3") should be Worksheets("main").Range("A3").Copy– Darren Bartrup-Cook
Nov 21 at 9:46
Copy Worksheets("main").Range("A3") should be Worksheets("main").Range("A3").Copy– Darren Bartrup-Cook
Nov 21 at 9:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well.
– Pawel Czyz
Nov 21 at 9:56
Should it be
selectedRows.Copy Worksheets("main").Range("A3")?– SJR
Nov 21 at 12:24
Should it be
selectedRows.Copy Worksheets("main").Range("A3")?– SJR
Nov 21 at 12:24
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53407496%2ffailed-selection-on-union-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
You should test if
selectedRows = Nothingbefore Selecting. Also, isWorksheets("contacts")hidden by any chance? One more thought try starting withSet selectedRows = Worksheets("contacts").Range("A1")as you testB1in your loop and not sure if you can have it in your selected range twice.– Tom
Nov 21 at 9:16
Without too much testing the code worked for me - except that last line which should be
Worksheets("main").Range("A3").Copy. Edit: But I did have the correct sheet selected - the code given by @PawelCzyz should work.– Darren Bartrup-Cook
Nov 21 at 9:43