VBA按用户范围选择排序 [英] VBA Sort by selected by user range

问题描述

过去三天来我一直在努力解决这个问题，所以请帮忙...

I've been struggling with this issue in the past 3 days, so please help...

我想做的是运行macro1时(出于参数考虑):

What I want to do is to when I run a macro1 (for the sake of the argument):

1. 将会弹出窗口，以选择应该对其中的单元格进行排序的范围
2. 已通过选择的最后一列(或第5列)(从最低到最高)对这些内容进行了排序

这里的问题是所选区域会改变传送时间(我在excel中创建了类似树的内容)，因此它不能是需要按最后一个(在此情况下为第5个)排序的特定列.选择(在下面的代码中，我不知道如何更改I11:I15)

The issue here is that selected area would change eveytime (I create something like a tree in excel), so it cannot be a specific column that needs to be sorted by the last one (or the 5th in this case) of the selected (in the code below I do not know how to change I11:I15)

我得到的东西不起作用:

What I got and it does not work:

Sub RangeSelectionPrompt()
    Dim rngStart As Range
    Set rngStart = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

    Set rngStart = Selection

    rngStart.Select
    ActiveWorkbook.Worksheets("CALCULATION").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CALCULATION").Sort.SortFields.Add Key:=Range( _
        "I11:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CALCULATION").Sort
        .SetRange rngStart
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

推荐答案

您可以使用以下方法将 rngStart 的结尾列作为范围:

You can get the end column of rngStart as a Range with:

rngStart.Columns(rngStart.Columns.Count)

使用 With 进行整理，您可以执行以下操作:

Using a With to tidy this up, you could do the following:

With rngStart
    ActiveWorkbook.Worksheets("CALCULATION").Sort.SortFields.Add Key:= _
        .Columns(.Columns.Count), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
End With

您也可以整理 ActiveWorkbook.Worksheets ，而不是使用 rngStart 的 Parent .

You could also tidy up the ActiveWorkbook.Worksheets by instead taking the Parent of rngStart.

最后，您想捕获如果用户单击取消"而不是选择范围会发生的错误.有很多方法可以做到这一点，但是想到的第一个方法是使用 On Error .. 陷阱.

Lastly, you want to trap the error that would occur if the user clicks Cancel instead of selecting a range. There are a number of ways of doing this but the first one that came to mind was using an On Error.. trap.

这是整个代码:

Sub RangeSelectionPrompt()

    Dim rngStart As Range
    Dim WS As Worksheet

    On Error Resume Next
    Set rngStart = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    Err.Clear
    On Error GoTo 0

    If rngStart Is Nothing Then
        MsgBox "User cancelled"
    Else
        Set WS = rngStart.Parent
        WS.Sort.SortFields.Clear

        With rngStart
            WS.Sort.SortFields.Add Key:= _
                .Columns(.Columns.Count), SortOn:=xlSortOnValues, Order:= _
                xlAscending, DataOption:=xlSortNormal
        End With

        With WS.Sort
            .SetRange rngStart
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End Sub

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