+ Reply to Thread
Results 1 to 4 of 4

Trying to sort using a variable for length of spreadsheet

Hybrid View

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    Irving, Tx
    MS-Off Ver
    Excel 2010
    Posts
    2

    Trying to sort using a variable for length of spreadsheet

    I am trying to perform a sort on a spreadsheet that is of varying lenght. There are 14 cells at the bottom that do not change and must remain at the bottom of the spreadsheet and unsorted. I am using a variable to determine the last row to be sorted. When I run the macro I get the error message "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank." Row 3 of the spreadsheet is my header row that I am using to sort by.


     Dim LastRow As Long
     
     LastRow = ActiveSheet.UsedRange.Rows.Count
     
     Dim Final As Long
     
     Final = LastRow - 14
     
     
        Range("A3:P" & Final).Select
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, _
            255)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255 _
            , 0)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
            255, 0)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("G4:G" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, _
            213, 180)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("M4:M" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("O4:O" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("J4:J" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("AAL ODR").Sort
            .SetRange Range("A3:P" & Final)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Trying to sort using a variable for length of spreadsheet

    you selected range A3:P" & Final, but you used it only at the end, why ?
    can you attach a sample file for testing ?
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Trying to sort using a variable for length of spreadsheet

    perhaps
    Sub CountProjects()
    
        Dim LastRow                    As Long
        Dim Final                      As Long
        Dim wsODR                      As Worksheet
    
        Set wsODR = ActiveWorkbook.Worksheets("AAL ODR")
    
        With wsODR
            
            With .UsedRange
                LastRow = .Rows.Count - .Row + 1
            End With
            Final = LastRow - 14
            
            With .Sort
                With .SortFields
                    .Clear
                    .Add(wsODR.Range("H4:H" & Final), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
                    .Add(wsODR.Range("H4:H" & Final), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
                    .Add(wsODR.Range("H4:H" & Final), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)
                    .Add(wsODR.Range("G4:G" & Final), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, 213, 180)
                    .Add Key:=wsODR.Range("M4:M" & Final), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .Add Key:=wsODR.Range("O4:O" & Final), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .Add Key:=wsODR.Range("J4:J" & Final), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                .SetRange wsODR.Range("A3:P" & Final)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End Sub
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    10-21-2013
    Location
    Irving, Tx
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Trying to sort using a variable for length of spreadsheet

    Problem has been solved. My boss neglected to tell me there were hidden formulas in the spreadsheet (several screens down from the data I was trying to sort!) Once I compensated for those everything works great. Thanks for the assistance. Here how my final code looks:

    Sub Macro1()
    '
    ' Keyboard Shortcut: Ctrl+Shift+S
    '
     Dim LastRow As Long
     
    I = 4
    Do
        If IsEmpty(Cells(I, "A")) Then
            Exit Do
    End If
        I = I + 1
    Loop
    
    
     Final = I - 14
     
        Range("A3:P" & Final).Select
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, _
            255)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255 _
            , 0)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
            255, 0)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("G4:G" & Final), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, _
            213, 180)
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("M4:M" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("O4:O" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("J4:J" & Final _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("AAL ODR").Sort
            .SetRange Range("A3:P" & Final)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
       
       End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-21-2012, 03:28 PM
  2. Replies: 6
    Last Post: 04-14-2012, 04:07 AM
  3. Sort Ascending for variable Report length
    By Hblbs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2009, 08:33 AM
  4. [SOLVED] Max/Min for variable length columns
    By Tim Rush in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-05-2006, 12:40 PM
  5. Sum a column of variable length?
    By Brian in forum Excel General
    Replies: 5
    Last Post: 02-03-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1