用动态列对数据排序
用动态列对数据排序
提问于 2021-08-23 21:42:07
我有一些值是从另一个宏粘贴过来的，总是从C6开始，从第6-11行开始，但是每次列数都会改变。
我想做的是按照第11行的值对数据表进行排序，从左到右，从小到大。所以排序范围总是"C6:?11“。
下面是我现在掌握的内容，主要来自一个带有几个编辑的记录宏：
Dim active As Worksheet
Set active = ActiveSheet
Range("C6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
active.Sort.SortFields.Clear
active.Sort.SortFields.Add2 Key:=Range( _
"C11:I11"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With active.Sort
.SetRange Range("C6:I11")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With我相信问题在于"C11:I11“部分，但我不知道该把它改为什么。代码看起来也很混乱，所以如果有更好的方法来写这个，那就太棒了。
我对VBA非常陌生，任何帮助都将不胜感激！
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-08-24 02:44:45
排序列
第一列HardCoded
Option Explicit
Sub SortMyRange()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim crg As Range ' Columns Range
Set crg = RefColumns(ws.Range("B6:B11"))
Dim srg As Range ' Sort Range
Set srg = crg.Resize(, crg.Columns.Count - 1).Offset(, 1)
Dim krg As Range
Set krg = srg.Rows(srg.Rows.Count) ' Key Range
Debug.Print crg.Address, srg.Address, krg.Address
With ws.Sort
With .SortFields
.Clear
.Add2 Key:=krg, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
End With
.SetRange srg
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: Creates a reference to the range from a column range
' ('FirstColumnRange') to the column range containing
' the right-most non-empty cell in the given column's rows.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefColumns( _
ByVal FirstColumnRange As Range) _
As Range
With FirstColumnRange.Columns(1)
Dim lCell As Range
Set lCell = .Resize(, .Worksheet.Columns.Count - .Column + 1) _
.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
If lCell Is Nothing Then Exit Function ' empty range
Set RefColumns = .Resize(, lCell.Column - .Column + 1)
End With
End FunctionFirst Cell HardCoded (初始CurrentRegion版本)
Option Explicit
Sub SortMyRange()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim crrg As Range ' Current Region Range
Set crrg = ws.Range("B6").CurrentRegion
Dim srg As Range ' Sort Range
Set srg = crrg.Resize(, crrg.Columns.Count - 1).Offset(, 1)
Dim krg As Range ' Key Range
Set krg = srg.Rows(srg.Rows.Count) ' bottom-most row
Debug.Print srg.Address, krg.Address
With ws.Sort
With .SortFields
.Clear
.Add2 Key:=krg, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
End With
.SetRange srg
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68899329
复制相关文章
相似问题
腾讯云开发者
