在更改的范围中使用VBA中的排序 [英] Using Sort in VBA for a Range that Changes

问题描述

每次运行代码时，VBA中的单元格范围都会发生变化。我试图编写代码，以便该范围按列F排序。

我遇到的问题是它只能是这个特定范围的单元格。此范围下还有其他单元格，我不想分类，并且此范围的大小会发生变化。部分代码如下。
$ b

 范围（Selection，Selection.End（xlToRight））。选择
 Range（Selection，Selection.End（xlDown））。选择
 
 vtools =选择
 
 ActiveWorkbook.Worksheets（异常每周汇总）Sort.SortFields。添加键_ 
：= Range（vtools），SortOn：= xlSortOnValues，Order：= xlAscending，_ 
 DataOption：= xlSortNormal 
使用ActiveWorkbook.Worksheets（Exceptions Weekly Summary）。 
 .SetRange Range（B11：H14）
 .Header = xlGuess 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
 .Apply 
 End With 
  

这是行不通的。

我不能直接引用单元格（例如，我不能使用Range（F2：F5）），因为这些单元格在每次运行代码时位于不同的位置。我知道如何找到我需要排序的内容，甚至选择它，但我无法告诉sort函数将哪个列排序。

有人可以帮助我这个？如果我理解正确，这将有所帮助。如果我理解正确，这将有所帮助。它找出所选区域的行号，然后用这些数字在列F中作出一个范围，并将其用作订购的关键字。

  Sub sortOnlySelectedArea（）
 
 Dim actSheet As Worksheet 
 Dim upper，lower As Integer 
 Dim tempString As String 
 Dim selectedArea As Range 
 
设置actSheet = Application.Worksheets（Sheet1）
 
'在这里你必须把你的部分做出正确的选择
 actSheet.Range（E5：G6 ）。选择
 Set selectedArea =选择
 $ b upper = selectedArea.Row 
 lower = upper + selectedArea.Rows.Count  -  1 
 
 tempString = F& CStr（上）和& ：F& CStr（lower）
 actSheet.Sort.SortFields.Clear 
 actSheet.Sort.SortFields.Add Key：= Range（tempString），_ 
 SortOn：= xlSortOnValues，Order：= xlAscending，DataOption ：= xlSortNormal 
 with actSheet.Sort 
 .SetRange selectedArea 
 .Header = xlGuess 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
 .Apply 
 End With 
 
 End Sub 
  

I have a range of cells in VBA that changes each time the code is run. I am trying to write code so that this range is sorted by column F.

The problem I am having is that it can only be this specific range of cells. There are other cells underneath this range that I do not want sorted, and this range changes in size. Part of the code is below. This is what I have tried so far with no luck.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

vtools = Selection

ActiveWorkbook.Worksheets("Exceptions Weekly Summary").Sort.SortFields.Add Key _
    :=Range(vtools), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Exceptions Weekly Summary").Sort
    .SetRange Range("B11:H14")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

This does not work.

I cannot directly reference the cells (I cannot use Range("F2:F5") for example) because these cells are in different places each time the code is run. I know how to find what I need sorted, and even select it, but I am having trouble telling the sort function which column to sort by.

Can someone help me with this? Thank you so much in advance!

解决方案

If I understood correctly this will help. It finds out the row numbers of the selected area and then makes a range in column F with these numbers and uses this as the key for ordering.

Sub sortOnlySelectedArea()

Dim actSheet As Worksheet
Dim upper, lower As Integer
Dim tempString As String
Dim selectedArea As Range

Set actSheet = Application.Worksheets("Sheet1")

' here you have to put in your part to make the right selection
actSheet.Range("E5:G6").Select
Set selectedArea = Selection

upper = selectedArea.Row
lower = upper + selectedArea.Rows.Count - 1

tempString = "F" & CStr(upper) & ":F" & CStr(lower)
actSheet.Sort.SortFields.Clear
actSheet.Sort.SortFields.Add Key:=Range(tempString), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With actSheet.Sort
    .SetRange selectedArea
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

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