对语法VBA进行排序的最有效方法

本文介绍了对语法VBA进行排序的最有效方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对VBA宏非常陌生，擅长使用excel.到目前为止，该站点已经非常有用.我有一个宏，该宏在最后一列之后添加四个列标题，然后在满足特定条件的情况下填充这些列，该部分可以正常工作.在填充列之前，我需要对数据进行排序.我当前的数据排序方法是基于记录宏并更改所需的变量.我读过，excel经常会非常低效地记录宏.我有点坦率地说.以下代码有效.

I am very new to VBA macros for excel. This site has been extraordinarily helpful thus far. I have a macro that adds four column headings after the last column, then fills these columns if a certain criteria is met, that part works fine. Before the columns can be filled I need to sort the data. My current method for sorting the data is based off of recording a macro, and changing the needed variables. I have read that often excel records macros very inefficiently. I kind of frankensteined this together. The following code works.

Sub ineffiecientway() Dim colltr As String colltr = Replace(Cells(1, LastColumn).Address(True, False), "$1", "") '<-Input column index, returns column letter Columns("A:" & colltr).Select ActiveWorkbook.Worksheets("DSEG").Sort.SortFields.Clear ActiveWorkbook.Worksheets("DSEG").Sort.SortFields.Add Key:=Range("A:A") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("DSEG").Sort.SortFields.Add Key:=Range("J:J") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("DSEG").Sort .SetRange Range("A:" & colltr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

下面的代码是我一直在努力的工作，然后拔出头发.我确定我会犯一百万个菜鸟错误.我认为这可能与我失败的.sort语法有关.

The code below is what I have been working on, and pulling my hair out. I'm sure I am making a million rookie mistakes. I think this may all be about syntax for .sort that I am failing at.

注意:

• GCI()是用户定义的功能，用于搜索第一行中的输入并返回列索引

• GCI() is a user defined fucntion that searches for the input in row one and returns the column index

LastRow()是用户定义的函数，它返回输入的列索引的最后一行.

LastRow() is a user defined function that returns the last row of the column index that is input.

LastColumn仅返回第一行中最后使用的列

LastColumn just returns the last used column in row one

Sub ThisDoesntWork() Dim ws As Worksheet Dim rngAll As Range Dim Col1 As Long 'for sort key1 Dim Row1 As Long Dim Col2 As Long 'for sort key2 Dim Row2 As Long Dim rng1 As Range Dim rng2 As Range Dim LastCell As Range Set ws = Worksheets("DSEG") Set LastCell = ws.Cells(LastRow(LastColumn), LastColumn) Col1 = GCI("CDate") Row1 = LastRow(Col1) Col2 = GCI("Start Time") Row2 = LastRow(Col2) Set rngAll = ws.Range(ws.Cells(1, 1), LastCell) Set rng1 = ws.Range(ws.Cells(1, Col1), ws.Cells(Row1, Col1)) Set rng2 = ws.Range(ws.Cells(1, Col2), ws.Cells(Row2, Col2)) MsgBox rng1.Address MsgBox rng2.Address MsgBox rngAll.Address With rngAll .Sort key1:=Range(rng1), order1:=xlAscending, DataOption1:=xlSortNormal, _ key2:=.Range(rng2), order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, _ Header:=xlYes End With

当我运行此代码时，它在".sort"处停止，错误为运行时错误'1004':对象'_Global'的方法"Range"失败我也尝试过使用"DataOption1:= xlSortNormal"，因为我不认为第一个范围需要以数字对文本进行排序，而这两者都会导致相同的错误.我在尝试上面的代码时没有设置范围或昏暗"工作表，并认为在运行代码之前设置范围会有所帮助.我为范围添加了MsgBox，以确保它们是我想要的范围.

When I run this code it stops at the ".sort" with the error "Run-time error '1004': Method "Range' of object'_Global' failed I have also tried with "DataOption1:=xlSortNormal" because I don't believe the first range needs to be sort text as numbers, both cause the same error. I was trying the above code without setting ranges or "Dim"ing worksheet and thought that setting the ranges prior to running the code would help. I added MsgBox for the ranges to make sure they are the ranges I want.

• 第一个MsgBox返回$ A $ 1:$ A $ 38061

• First MsgBox returns $A$1:$A$38061

第二个MsgBox返回$ J $ 1:$ J $ 38061

Second MsgBox returns $J$1:$J$38061

第三MsgBox返回$ A $ 1:$ S $ 38061

Third MsgBox returns $A$1:$S$38061

前两个是我要排序的范围，最后一个是我要排序的所有数据的范围，这些是正确的范围.

The first two are the ranges that I want to sort by, the last is the range of all the data I want to sort, these are the correct ranges.

我们将不胜感激任何建议或帮助.另外，关于更好发布的任何建议，因为我敢肯定正确发布格式"也会出现错误.

Any advice or help getting this working would be greatly appreciated. Also, any advice on better posting, because I'm sure a made mistakes on the "Proper posting format" as well.

感谢Nanashi，我将不重复功能，感谢技巧.谢谢吉普.Current区域对它进行了很多清理.正是这个.columns修复了错误(我正在尝试.range)Million感谢你们俩.工作代码如下.

Thanks Nanas I will not repeat functions, I appreciate the tip. Thanks Jeeped. The Current region bit cleaned it up a lot. And it was the .columns that fixed the error (I was trying .range) Million thanks to you both. The working code is below.

Dim ws As Worksheet Dim Col1 As Long Dim Col2 As Long Set ws = Worksheets("DSEG") Col1 = GCI("CDate") 'searches string and returns column index Col2 = GCI("Start Time") 'searches string and returns column index With ws.Cells(1, 1).CurrentRegion.Cells .Sort key1:=.Columns(Col1), order1:=xlAscending, DataOption1:=xlSortNormal, _ key2:=.Columns(Col2), order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, Header:=xlYes End With

推荐答案

通常，我 .Sort 的任何区域都没有任何完全空白的行或列，这些行或列将 .CurrentRegion ，所以我用它来定义要排序的范围. .Cells(1,1).CurrentRegion 等同于选择A1并点击 Ctrl + A .它包含从A1扩展到到达右侧的完全空白列或向下的完全空白行的数据 island .

Typically, any region I .Sort does not have any fully blank rows or columns breaking up the .CurrentRegion so I use that to define the range to be sorted. The .Cells(1,1).CurrentRegion is the equivalent of selecting A1 and tapping Ctrl+A. It encompasses the island of data that expands from A1 until it reaches a fully blank column to the right or a fully blank row down.

Sub prettyefficient() With Sheets("DSEG").Cells(1, 1).CurrentRegion.Cells .Sort Key1:=.Columns(1), Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers, _ Key2:=.Columns(10), Order2:=xlAscending, DataOption2:=xlSortTextAsNumbers, _ Orientation:=xlTopToBottom, Header:=xlYes End With End Sub

单个命令中最多可以使用三个键( Key1 ， Key2 和 Key3 ).如果您还需要更多，请将其分成两个命令.

You can use up to three keys (Key1, Key2 & Key3) in a single command. If you require more than that, break it into two commands.