Excel VBA Sort with formulas - Error 1004

Question

I have checked all of the forums questions that relate to this topic. I am unable to get my code to work.

Here is the code. Please let me know what I need to correct.

Dim strDataRange As String
Dim strKeyRange As String

strDataRange = "B" & strStartRow & ":M" & strLastRow
strKeyRange = "B" & strStartRow & ":B" & strLastRow

Range(strDataRange).Sort Key1:=strKeyRange, Order1:=xlDescending,   Header:=xlNo

The strDataRange is B5:M18.

The strKeyRange is B5:B18.

DisplayName · Accepted Answer · 2018-09-15 15:30:38Z

1

use:

Range(strDataRange).Sort Key1:=Range(strKeyRange), Order1:=xlDescending, Header:=xlNo

since Sort method wants Key1parameter as "...sort field, either as a range name (String) or Range object"

answered Sep 15, 2018 at 15:30

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EddiRae Over a year ago

I changed to be "Range(strDataRange).Sort Key1:=Range(strKeyRange), Order1:=xlDescending, Header:=xlNo" This is still giving me an error 1004: "This operation requires the merged cells to be identically sized." I don't have any merged cells in the sort range.

DisplayName Over a year ago

Do you have any merged cell in B5:M18?

EddiRae Over a year ago

No. No merged cells at all in the sort range as stated above.

EddiRae · Accepted Answer · 2018-09-17 02:06:50Z

0

I found the issue. Since the VBA codes was on a different worksheet than where I was sorting, I needed to add the workbook reference; which is "ws".

here is the corrected code

 ws.Range(strDataRange).Sort Key1:=ws.Range(strKeyRange), Order1:=xlDescending, Header:=xlNo

Thanks for all your help.

answered Sep 17, 2018 at 2:06

EddiRae

478 bronze badges

Collectives™ on Stack Overflow

Excel VBA Sort with formulas - Error 1004

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related