Excel 2010 Vba排序动态范围名称

本文介绍了Excel 2010 Vba排序动态范围名称的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

问题，我如何确定命名范围的第一行。如果我在表单中添加行，则我的 "F50"会更改。我想以编程方式识别第一行。下面的代码与强制"F50"一起使用。 locatons。
如果有更好的方式来写它，请告诉我。我调整了一个宏来达到这一点。谢谢

Question, How can I determine the first row of a named range. If I add rows to the sheet, My "F50" will change. I would like to programmically ID the first row. The code below works with the forced "F50" locatons. If there is a better way to write it please let me know. I tweaked a macro to get to this point. Thanks

Dim RowCount As String

Dim RowCnt As String

Dim RowCount As String
Dim RowCnt As String

   范围（"历史记录"）。选择

   &NBSP; RowCount =表格（"时间"）。范围（"B"& Rows.Count）.End（xlUp）.Row

   &NBSP;&NBSP;&NBSP; 'MsgBox（RowCount）

    RowCnt =（" F50：F"&（RowCount））

    &NBSP; 'MsgBox（RowCnt） '置信度检查

    Range("History").Select
   RowCount = Sheets("Time").Range("B" & Rows.Count).End(xlUp).Row
       'MsgBox (RowCount)
    RowCnt = ("F50:F" & (RowCount))
    ' MsgBox (RowCnt) ' Confidence check

    ActiveWorkbook.Worksheets（" TIME"）。Sort.SortFields.Clear

    ActiveWorkbook.Worksheets（" TIME"）。Sort.SortFields.Add Key：= Range（" F50：F"&（RowCount）），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

   使用ActiveWorkbook.Worksheets（"TIME"）。排序

        .SetRange范围（"B50：L"&（RowCount））

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        。应用

   结束

    ActiveWorkbook.Worksheets("TIME").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TIME").Sort.SortFields.Add Key:=Range("F50:F" & (RowCount)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TIME").Sort
        .SetRange Range("B50:L" & (RowCount))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

抬头

推荐答案

HTH

Harry

Sub SortMyData()
' This is macro sorts a Dynamic data area that starts from Col A1 to any other Column.
' The Row & Column selection is Dynamic as the file could be 1000 rows today and 800
' rows tomorrow and 1200 the next day
' It sorts field in Column R and sorts in descending sequence
' Row 1 contains Headers and the data is in Sheet1 of the spreadsheet
'
'******************************* Define variables for the data that I want to store for later use
Dim MyDataFirstCell
Dim MyDataLastCell
Dim MySortCellStart
Dim MySortCellEnd

Windows("CustomerOrdersGreaterThanFC.xlsx").Activate ' Go to my data file

'************************** Establish the Data Area
Range("A1").Select 'Get to the first cell of data area
MyDataFirstCell = ActiveCell.Address 'Get the first cell address of Data Area
Selection.End(xlDown).Select 'Get to Bottom Row of the data
Selection.End(xlToRight).Select 'Get to the last Column and data cell by heading to the right-hand end
MyDataLastCell = ActiveCell.Address 'Get the Cell address of the last cell of my data area

'************************** Establish the Sort column first and last data points.
Range("R2").Select 'Get to first cell of data sort Column (Example Col 'R' Row 2 because Row 1 contains the header)
MySortCellStart = ActiveCell.Address 'Get the Cell address of the first cell of my data sort Column
Selection.End(xlDown).Select 'Get to the bottom Row of data
MySortCellEnd = ActiveCell.Address 'Get the Cell address of the last cell of my sort Column

'************************** Start the sort by specifying sort area and columns
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
Key:=Range(MySortCellStart & ":" & MySortCellEnd), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(MyDataFirstCell & ":" & MyDataLastCell)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

这篇关于Excel 2010 Vba排序动态范围名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

rowCount