对语法VBA进行排序的最有效方法 [英] Most efficient way to sort and sort syntax 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.

这篇关于对语法VBA进行排序的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！