Excel 2010 Vba Sort Dynamic Range name RRS feed

  • Question

  • 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

         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.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
        End With

    heads up

    Tuesday, February 4, 2014 12:58 AM

All replies

  • I found this on the internet some time back.  You should be able to modify it to suit your needs.



    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.Add _
    Key:=Range(MySortCellStart & ":" & MySortCellEnd), SortOn:=xlSortOnValues, Order:=xlDescending, _
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range(MyDataFirstCell & ":" & MyDataLastCell)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    End With
    End Sub 

    • Proposed as answer by psytajl Wednesday, March 19, 2014 12:07 PM
    Wednesday, February 5, 2014 1:18 AM