当前位置：文江博客话题详情

使用VBA进行排序操作？

发布于 2024-11-27 18:20:07 字数 92 浏览 1 评论 0原文

如何使用 VBA 代码执行排序操作？任何定义的宏来执行该操作？

我必须按升序对 B 列进行排序，其中包含值。 C 栏内有日期，并且以最旧到最新的方式排列。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星星的軌跡 2024-12-04 18:20:07

我强烈建议您使用宏记录器。如果您在 Excel 中没有看到“开发人员”选项卡，请转到 Excel 选项，它应该位于“常用”选项卡中。单击将其打开。

按“录制宏”，然后执行您想做的操作（例如突出显示某一列并按功能区上的“排序”并按升序排序），然后“停止录制”。进入 VBA (Alt+F11) 并查看生成的代码，以了解如何编写相同函数的语法。

例如，对日期进行排序将导致如下结果：

Sub Macro1()
    Columns("F:F").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1:F5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("F1:F5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

由于在运行宏时您已经选择了要排序的列，因此您可以将代码清理为如下所示：

Sub SortAscending()
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

可以使用相同的代码来完成日期排序，因为它是升序：

诀窍是记住您不需要在已经选择的代码中选择范围。祝你好运，享受录音机的乐趣，它在这种情况下确实很有帮助。

此外，还有很多有关 Excel VBA 的书籍，我建议您在书店或以电子方式购买一本。大多数书籍都会引导您了解 VBA 基础知识、主要关键字、使用记录器，然后最终足够深入，以便您可以编写自己的代码（记录器代码很难优化或完美，但对于学习措辞非常有用）（语法）用于执行一些工作表函数）。

I highly reccomend you play around with the macro recorder. If you do not see a developer tab in your Excel, go to excel options and it should be in the Popular tab. Click to turn it on.

Press Record Macro, then do the thing you want to do (like highlight a column and press Sort on the ribbon and sort by ascending) then Stop Recording. Go into VBA (Alt+F11) and see the resulting code to learn the syntax on how to write the same function.

For example, sort dates will result in something like this:

Sub Macro1()
    Columns("F:F").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1:F5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("F1:F5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Since you will already select the column you want to sort when you run your macro, you can clean the code up to something like this:

Sub SortAscending()
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sorting dates can be done with the same codes since it's ascending order:

The trick is to remember that you don't need to select the range in the code that you are already selecting. Good luck and have fun with the recorder, it really does help in cases like this.

Also, there are numerous books on Excel VBA and I reccomend you picking one up at the book store or digitally. Most books out there will walk you through the basic of understanding VBA basics, main keywords, using the recorder, then eventually far along enough so you can write your own code (recorder code is hardly optimized or perfect, but is great for learning the wording (syntax) for doing some worksheet functions).

回复收藏 0 原文

~没有更多了~