Results 1 to 2 of 2

Sort words between strings "Start" and "End"

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Sort words between strings "Start" and "End"

    I have to sort every value after row 9 which contains "Start" and before last text string "End" (two rows above "End") in three different sheets. This code works if "End" is hidden, but I would like to do this when "End" is shown instead. Otherwise "End" is sorted with the values after row 9.

    I want "End" (the last text string) to stay at the same place after the sorting, not to be affected by the code.

    Sub SortYear()
        Dim xlSort As XlSortOrder
        Dim e
    
        For Each e In Array(Array("SheetA"), Array("SheetB"), Array("SheetC"))
            With Sheets(e(0))
                .Unprotect msPASSWORD
                    If (StrComp(UCase(.Range("A10").Value), UCase(.Range("A11").Value)) > 0) Then
                        xlSort = xlAscending
                    Else
                        xlSort = xlDescending
                    End If
        
                    With .Range("A10:XFD1048576")
                        .Sort Key1:=.Range("A10"), Order1:=xlSort, Header:=xlNo, _
                              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                              DataOption1:=xlSortNormal
                    End With
                .Protect Password:=msPASSWORD, DrawingObjects:=False, Contents:=True, Scenarios:=True
            End With
        Next e
        
        ActiveSheet.EnableSelection = xlNoRestrictions
        Sheets("SheetA").Activate
    End Sub
    Last edited by jokris; 04-07-2016 at 01:54 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. replace all defined range names in the sheet that start with "Street" to "Road"
    By matrex in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-04-2016, 08:53 AM
  2. [SOLVED] Text strings separated with "/", ",", "-" or "&"
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-27-2015, 02:13 PM
  3. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Countdown Timer in Excel with a "pause" and "start" button
    By bbhagwat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2013, 05:49 AM
  6. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  7. Replies: 5
    Last Post: 10-12-2010, 06:46 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