VBA: Sorting worksheets with macro
1
As shown in this image
my sort syntax is wrong and I don't understand why. I get runtime error 1004: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"
Sub Sort()
'
' Sort Macro
Dim rowNum As Variant
Dim columnNum As Variant
Dim sortField As Range
Dim keySort As Range
rowNum = Worksheets("Updated 1.0").Range("A1").End(xlDown).row
MsgBox (rowNum)
columnNum = Worksheets("Updated 1.0").Range("A1").End(xlToRight).column
MsgBox (columnNum)
With Worksheets("Updated 1.0")
Set sortField = Range(.Cells(2, 1), .Cells(rowNum, columnNum))
Set keySort = Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False,
Orientation:=xlSortRows
End With
excel vba excel-vba sorting
edited Nov 20 at 17:54
FreeMan
4,83212147
asked Nov 20 at 17:02
ethan finger
64
add a comment |
1
As shown in this image
my sort syntax is wrong and I don't understand why. I get runtime error 1004: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"
Sub Sort()
'
' Sort Macro
Dim rowNum As Variant
Dim columnNum As Variant
Dim sortField As Range
Dim keySort As Range
rowNum = Worksheets("Updated 1.0").Range("A1").End(xlDown).row
MsgBox (rowNum)
columnNum = Worksheets("Updated 1.0").Range("A1").End(xlToRight).column
MsgBox (columnNum)
With Worksheets("Updated 1.0")
Set sortField = Range(.Cells(2, 1), .Cells(rowNum, columnNum))
Set keySort = Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False,
Orientation:=xlSortRows
End With
excel vba excel-vba sorting
edited Nov 20 at 17:54
FreeMan
4,83212147
asked Nov 20 at 17:02
ethan finger
64
3
Rangebeing unqualified is referring to theActiveSheet. If that's not theWithsheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.
– Mathieu Guindon
Nov 20 at 17:06
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent ofsortfieldis stillUpdated 1.0. After saying that - it's still, better to fully qualify everything.
– Darren Bartrup-Cook
Nov 20 at 17:30
2
@DarrenBartrup-Cook In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1) = 42throws error 1004. Same in the code-behind forThisWorkbook, and ditto in a standard module. In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42throws error 1004 (because of the implicitMequalifier), but works inThisWorkbookand in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)
– Mathieu Guindon
Nov 20 at 17:37
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicitMequalifier when used behind a sheet, but had forgotten as I always qualify the ranges.
– Darren Bartrup-Cook
Nov 21 at 9:10
add a comment |
1
1
1
As shown in this image
my sort syntax is wrong and I don't understand why. I get runtime error 1004: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"
Sub Sort()
'
' Sort Macro
Dim rowNum As Variant
Dim columnNum As Variant
Dim sortField As Range
Dim keySort As Range
rowNum = Worksheets("Updated 1.0").Range("A1").End(xlDown).row
MsgBox (rowNum)
columnNum = Worksheets("Updated 1.0").Range("A1").End(xlToRight).column
MsgBox (columnNum)
With Worksheets("Updated 1.0")
Set sortField = Range(.Cells(2, 1), .Cells(rowNum, columnNum))
Set keySort = Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False,
Orientation:=xlSortRows
End With
excel vba excel-vba sorting
edited Nov 20 at 17:54
FreeMan
4,83212147
asked Nov 20 at 17:02
ethan finger
64
As shown in this image
my sort syntax is wrong and I don't understand why. I get runtime error 1004: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"
Sub Sort()
'
' Sort Macro
Dim rowNum As Variant
Dim columnNum As Variant
Dim sortField As Range
Dim keySort As Range
rowNum = Worksheets("Updated 1.0").Range("A1").End(xlDown).row
MsgBox (rowNum)
columnNum = Worksheets("Updated 1.0").Range("A1").End(xlToRight).column
MsgBox (columnNum)
With Worksheets("Updated 1.0")
Set sortField = Range(.Cells(2, 1), .Cells(rowNum, columnNum))
Set keySort = Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False,
Orientation:=xlSortRows
End With
excel vba excel-vba sorting
excel vba excel-vba sorting
edited Nov 20 at 17:54
FreeMan
4,83212147
asked Nov 20 at 17:02
ethan finger
64
edited Nov 20 at 17:54
FreeMan
4,83212147
asked Nov 20 at 17:02
ethan finger
64
edited Nov 20 at 17:54
FreeMan
4,83212147
edited Nov 20 at 17:54
FreeMan
4,83212147
edited Nov 20 at 17:54
FreeMan
4,83212147
4,83212147
asked Nov 20 at 17:02
ethan finger
64
asked Nov 20 at 17:02
ethan finger
64
asked Nov 20 at 17:02
ethan finger
64
64
3
Rangebeing unqualified is referring to theActiveSheet. If that's not theWithsheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.
– Mathieu Guindon
Nov 20 at 17:06
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent ofsortfieldis stillUpdated 1.0. After saying that - it's still, better to fully qualify everything.
– Darren Bartrup-Cook
Nov 20 at 17:30
2
@DarrenBartrup-Cook In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1) = 42throws error 1004. Same in the code-behind forThisWorkbook, and ditto in a standard module. In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42throws error 1004 (because of the implicitMequalifier), but works inThisWorkbookand in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)
– Mathieu Guindon
Nov 20 at 17:37
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicitMequalifier when used behind a sheet, but had forgotten as I always qualify the ranges.
– Darren Bartrup-Cook
Nov 21 at 9:10
add a comment |
3
Rangebeing unqualified is referring to theActiveSheet. If that's not theWithsheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.
– Mathieu Guindon
Nov 20 at 17:06
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent ofsortfieldis stillUpdated 1.0. After saying that - it's still, better to fully qualify everything.
– Darren Bartrup-Cook
Nov 20 at 17:30
2
@DarrenBartrup-Cook In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1) = 42throws error 1004. Same in the code-behind forThisWorkbook, and ditto in a standard module. In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42throws error 1004 (because of the implicitMequalifier), but works inThisWorkbookand in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)
– Mathieu Guindon
Nov 20 at 17:37
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicitMequalifier when used behind a sheet, but had forgotten as I always qualify the ranges.
– Darren Bartrup-Cook
Nov 21 at 9:10
3
3
Range being unqualified is referring to the ActiveSheet. If that's not the With sheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.– Mathieu Guindon
Nov 20 at 17:06
Range being unqualified is referring to the ActiveSheet. If that's not the With sheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.– Mathieu Guindon
Nov 20 at 17:06
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent of
sortfield is still Updated 1.0. After saying that - it's still, better to fully qualify everything.– Darren Bartrup-Cook
Nov 20 at 17:30
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent of
sortfield is still Updated 1.0. After saying that - it's still, better to fully qualify everything.– Darren Bartrup-Cook
Nov 20 at 17:30
2
2
@DarrenBartrup-Cook In the code-behind for
Sheet1, doing Range(Sheet2.Cells(1,1) = 42 throws error 1004. Same in the code-behind for ThisWorkbook, and ditto in a standard module. In the code-behind for Sheet1, doing Range(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42 throws error 1004 (because of the implicit Me qualifier), but works in ThisWorkbook and in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)– Mathieu Guindon
Nov 20 at 17:37
@DarrenBartrup-Cook In the code-behind for
Sheet1, doing Range(Sheet2.Cells(1,1) = 42 throws error 1004. Same in the code-behind for ThisWorkbook, and ditto in a standard module. In the code-behind for Sheet1, doing Range(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42 throws error 1004 (because of the implicit Me qualifier), but works in ThisWorkbook and in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)– Mathieu Guindon
Nov 20 at 17:37
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicit
Me qualifier when used behind a sheet, but had forgotten as I always qualify the ranges.– Darren Bartrup-Cook
Nov 21 at 9:10
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicit
Me qualifier when used behind a sheet, but had forgotten as I always qualify the ranges.– Darren Bartrup-Cook
Nov 21 at 9:10
add a comment |
1 Answer
1
active
oldest
votes
2
You were missing some . inside the With but also the sort was was not correct I think.
This worked for me:
Sub Sort()
Dim sht As Worksheet
Dim sortField As Range
Dim keySort As Range
Set sht = Worksheets("Updated 1.0")
With sht
Set sortField = .Range("A1").CurrentRegion
Set keySort = .Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False, _
Orientation:=xlSortRows
End With
End Sub
answered Nov 20 at 17:06
Tim Williams
85k96785
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
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%2f53397975%2fvba-sorting-worksheets-with-macro%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
2
You were missing some . inside the With but also the sort was was not correct I think.
This worked for me:
Sub Sort()
Dim sht As Worksheet
Dim sortField As Range
Dim keySort As Range
Set sht = Worksheets("Updated 1.0")
With sht
Set sortField = .Range("A1").CurrentRegion
Set keySort = .Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False, _
Orientation:=xlSortRows
End With
End Sub
answered Nov 20 at 17:06
Tim Williams
85k96785
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
add a comment |
2
You were missing some . inside the With but also the sort was was not correct I think.
This worked for me:
Sub Sort()
Dim sht As Worksheet
Dim sortField As Range
Dim keySort As Range
Set sht = Worksheets("Updated 1.0")
With sht
Set sortField = .Range("A1").CurrentRegion
Set keySort = .Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False, _
Orientation:=xlSortRows
End With
End Sub
answered Nov 20 at 17:06
Tim Williams
85k96785
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
add a comment |
2
2
2
You were missing some . inside the With but also the sort was was not correct I think.
This worked for me:
Sub Sort()
Dim sht As Worksheet
Dim sortField As Range
Dim keySort As Range
Set sht = Worksheets("Updated 1.0")
With sht
Set sortField = .Range("A1").CurrentRegion
Set keySort = .Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False, _
Orientation:=xlSortRows
End With
End Sub
answered Nov 20 at 17:06
Tim Williams
85k96785
You were missing some . inside the With but also the sort was was not correct I think.
This worked for me:
Sub Sort()
Dim sht As Worksheet
Dim sortField As Range
Dim keySort As Range
Set sht = Worksheets("Updated 1.0")
With sht
Set sortField = .Range("A1").CurrentRegion
Set keySort = .Range("A1")
sortField.Sort Key1:=keySort, Order1:=xlDescending, MatchCase:=False, _
Orientation:=xlSortRows
End With
End Sub
answered Nov 20 at 17:06
Tim Williams
85k96785
edited Nov 20 at 17:25
answered Nov 20 at 17:06
Tim Williams
85k96785
answered Nov 20 at 17:06
Tim Williams
85k96785
answered Nov 20 at 17:06
Tim Williams
85k96785
85k96785
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
add a comment |
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
thanks but it still did not correct the error for the sort line in the with block
– ethan finger
Nov 20 at 17:16
See my update above
– Tim Williams
Nov 20 at 17:26
See my update above
– Tim Williams
Nov 20 at 17:26
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%2f53397975%2fvba-sorting-worksheets-with-macro%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
3
Rangebeing unqualified is referring to theActiveSheet. If that's not theWithsheet, error 1004 is thrown. This is one of the ~80 code inspections Rubberduck can warn you about.– Mathieu Guindon
Nov 20 at 17:06
@MathieuGuindon - that's something I've been wondering about since I saw a question with an unqualified range. If both cell references within the unqualified range are qualified then the range is also qualified. With a different sheet selected the parent of
sortfieldis stillUpdated 1.0. After saying that - it's still, better to fully qualify everything.– Darren Bartrup-Cook
Nov 20 at 17:30
2
@DarrenBartrup-Cook In the code-behind for
Sheet1, doingRange(Sheet2.Cells(1,1) = 42throws error 1004. Same in the code-behind forThisWorkbook, and ditto in a standard module. In the code-behind forSheet1, doingRange(Sheet2.Cells(1,1), Sheet2.Cells(2,1)) = 42throws error 1004 (because of the implicitMequalifier), but works inThisWorkbookand in a standard module. You know what, IMO that's knowledge that's dangerous to share (being confusing for inexperienced VBA devs). Qualify your ranges, period - then your code will work regardless of where & how it's written. ;-)– Mathieu Guindon
Nov 20 at 17:37
@MathieuGuindon Point well made and something I hadn't considered. I'm sure I'd read somewhere about the implicit
Mequalifier when used behind a sheet, but had forgotten as I always qualify the ranges.– Darren Bartrup-Cook
Nov 21 at 9:10