这个宏基于我记录的一个宏,但已调整以应对4个排序标准中的3个。我无法弄清楚为什么当特定范围标准时我的宏起作用,但是当我引用单个单元格并推断时。
使用此行,排序有效 .SetRange Range("A1:W162")
使用此行,它不排序。 .SetRange Range("A1").End(xlDown).End(xlToRight)
我已经踏过,可以确认它正在选择正确的范围
我不想将行与特定单元格一起使用,因为将来的导出尺寸会不同。
这是完整的子例程(相关位接近底部)。
谢谢!
Sub SortByScoreAndCost()
Dim Score As Range
Dim Cost As Range
Dim YN As Range
Dim OriginalScore As Range
Set Score = Cells.Find("Score")
Set Cost = Cells.Find("Cost")
Set YN = Cells.Find("Y/N")
Set OriginalScore = Cells.Find("Original Score")
Range("A1").CurrentRegion.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
Score.Offset(1, 0), Score.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
If Cells.Find("Cost") Is Nothing _
Then
Else: ActiveSheet.Sort.SortFields.Add Key:=Range _
(Cost.Offset(1, 0), Cost.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
End If
If Cells.Find("Y/N") Is Nothing _
Then
Else: ActiveSheet.Sort.SortFields.Add Key:=Range _
(YN.Offset(1, 0), YN.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
End If
If Cells.Find("Original Score") Is Nothing _
Then
Else: ActiveSheet.Sort.SortFields.Add Key:=Range _
(OriginalScore.Offset(1, 0), Original.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
End If
With ActiveSheet.Sort
.SetRange Range("A1:W162") 'works with this line
.SetRange Range("A1").End(xlDown).End(xlToRight) 'doesn't work if replaced with this line
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Range("A1").End(xlDown).End(xlToRight)
是指单个单元格范围,与 A1:W162
不同。假设A1:W162
是表格上所有数据的范围。该行仅指W162
。Range.End
属性在此处解释,并表明它没有保留起点。
尝试Range("A1:"& Range("A1").End(xlDown).End(xlToRight).Address)
,因为这应该创建类似于A1:W162
的范围。第一部分是字符串"A1:"
,Range("A1).End(xlDown).end(xlToRight).Address
一起返回字符串"$W$162"
,它们形成"A1:$W$162"
我对您的问题发表了评论,我会建议下面的代码。
Dim wsData as Worksheet 'Add worksheet variable
Set wsData = ThisworkBook.Worksheets("Name of your data sheet")
With wsData.Sort 'Instead of using ActiveSheet
.SetRange wsData.Range(wsData.Cells(1, 1), wsData.Cells(1, 1).End(xlDown).End(xlToRight))