Excel VBA按特定列排序命名范围 [英] Excel VBA Sorting a named range by a particular column

问题描述

我有一个选定的范围，在该范围内，我想按特定列进行排序，以便每行保持一致，即排序函数只是根据我选择排序的任何列重新排序整行。 / p>

我的代码到目前为止是这样的：

  Sub CustomSortAscendingExcel（ByVal control作为IRibbonControl，ByRef cancelDefault As Variant）
 Dim selRange As range 
 Dim usedRange As range 
 Dim checkRange As range 
 Dim shtData As Worksheet 
 Set shtData = Worksheets（数据）
 shtData.Activate 
 shtData.Unprotect 
设置selRange =选择
设置usedRange = returnUsedRange 
设置checkRange = Intersect（usedRange，selRange）
如果不是Application.Intersect（usedRange，selRange）Is Nothing Then 
 With ActiveSheet 
 usedRange.Sort Key1：= selRange，Order1：= xlAscending，DataOption1：= xlSortNormal，MatchCase：= False，SortMethod：= xlPinYin ，_ 
标题：= xlNo，方向：= xlSortColumns 
 .Sor t.Apply 
结束与
  

类似的排序降序。这对column1很好。我的问题是，对于列2,3,4，它只对那些在column1中没有值的行才能正常工作。例如，在下面的图像中，按列3排序，正确地重新排序前9行，而不是最后两个。

任何人都可以建议一种方法来解决这个问题？谢谢！

解决方案

我记录了一个选择Range（A1：D5）的简单宏，应用过滤器，然后按照第3列（C）

看看这个：

 范围（ A1：D5）。选择
 Selection.AutoFilter 
 ActiveWorkbook.Worksheets（Sheet1）。AutoFilter.Sort.SortFields.Clear 
 ActiveWorkbook.Worksheets（Sheet1）。AutoFilter.Sort .SortFields.Add Key：= Range _ 
（C1：C5），SortOn：= xlSortOnValues，Order：= xlAscending，DataOption：= _ 
 xlSortNormal 
使用ActiveWorkbook.Worksheets Sheet1）。AutoFilter.Sort 
 .Header = xlYes 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
。应用
结束有
  

现在，你不得不做很多事情来修复哟你的问题：）

I have a selected range and within that Range I would like to sort by a particular column so that each row is kept the same i.e. the sort function just reorders the entire rows based on whichever column I have selected to sort by.

My code so far is this:

Sub CustomSortAscendingExcel(ByVal control As IRibbonControl, ByRef cancelDefault As Variant)
Dim selRange As range
Dim usedRange As range
Dim checkRange As range
Dim shtData As Worksheet
Set shtData = Worksheets("Data")
shtData.Activate
shtData.Unprotect
Set selRange = Selection
Set usedRange = returnUsedRange
Set checkRange = Intersect(usedRange, selRange)
If Not Application.Intersect(usedRange, selRange) Is Nothing Then
With ActiveSheet
    usedRange.Sort Key1:=selRange, Order1:=xlAscending, DataOption1:=xlSortNormal, MatchCase:=False, SortMethod:=xlPinYin, _
    Header:=xlNo, Orientation:=xlSortColumns
    .Sort.Apply
End With

And similar for sorting Descending. This works fine for column1. My problem is that for columns 2, 3, 4 it only works correctly for those rows that have a value in column1 not for any that don't. For example in the image below sorting by column 3 correctly reorders the first 9 rows but not the last two.

Can anyone suggest a way to fix this? Thanks!

解决方案

I recorded a simple macro that selects Range("A1:D5") , applies filter, then sorts in ascending order based on the 3rd column (C)

look at this:

Range("A1:D5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C1:C5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Now, there isnt much you have to do to fix your problem :)

这篇关于Excel VBA按特定列排序命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！