+ Reply to Thread
Results 1 to 2 of 2

Sort function in excel

Hybrid View

  1. #1
    Registered User
    Join Date
    10-16-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    1

    Smile Sort function in excel

    i have a question about sor function in excel:

    i need to sort the column A:3 to A:100 from smallest to largest, but in this column A, i want that column A:20 would not be sorted and just leave it as it is.is it possible to do this?

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sort function in excel

    You'd need a macro to do it, but this should work...

    Sub SortWithException()
    
    Const sSORT_RANGE = "A1:A100"
    Const sEXCEPTION = "A20"
    
    Dim lExceptionValue As Long
    Dim rngMatchException As Range
    
    lExceptionValue = Range(sEXCEPTION).Value
    
    Range(sSORT_RANGE).Sort Range(sSORT_RANGE), xlAscending
    
    Set rngMatchException = Range(sSORT_RANGE).Find(lExceptionValue, lookat:=xlWhole)
    
    If rngMatchException Is Nothing Then
      'Oh dear
    Else
      rngMatchException.Delete shift:=xlShiftUp
      Range(sEXCEPTION).Insert shift:=xlShiftDown
      Range(sEXCEPTION).Value = lExceptionValue
    End If
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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