Results 1 to 10 of 10

Thread: Sorting a column that shifts down

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting a column that shifts down

    I have two separate fields of Data in this work book. The top one sorts just fine as the first row of data never moves. However, the second field of data shifts down every time a new entry is made into field 1. the formula I have right now is this:


    With Worksheets("Sheet1")
    LastRow = Cells(Rows.Count, 8).End(xlUp).Row
    Set b = Cells.Find("LCPO RECALL", LookIn:=xlValues, lookat:=xlWhole)
    Set a = b.Offset(1, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("T:T") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("a:T" & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End With

    any help would be greatly appreciated.

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a column that shifts down

    is this even possible to do?

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sorting a column that shifts down

    Can't you just move the last row test down?

    Code:
        With Worksheets("Sheet1")
    
            Set b = .Cells.Find("LCPO RECALL", LookIn:=xlValues, lookat:=xlWhole)
            Set a = b.Offset(1, 0)
            LastRow = .Cells(Rows.Count, 8).End(xlUp).Row
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("T:T") _
                                      , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange .Range("a:T" & LastRow)
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    Last edited by MARK858; May 16th, 2019 at 04:56 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a column that shifts down

    [QUOTE=MARK858;5278422]Can't you just move the last row test down?

    Mark,

    Please forgive my ignorance I don't know what you mean by moving the last row test down. I am still very new to VBA and I have probably been writing my code in the longest most convoluted way. Any help would be greatly appreciated as I feel I have been beating my head against a brick wall.

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sorting a column that shifts down

    What happened with the code I posted when you ran it? and what are the 2 variables a and b used for as they aren't used in the code you posted?

    also don't you have any issue with the line below?

    Code:
    .SetRange Range("a:T" & LastRow)
    You might need to explain in words exactly what you expect the code to do.
    Last edited by MARK858; May 16th, 2019 at 05:41 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a column that shifts down

    Yes, I am having a problem with that line. I am trying to set the first row of my sort range to the row right below the cell titled "LCPO RECALL" which is why I tried to set a=b.offset(1,0). However this doesn't seem to work. The reason I am doing this is because the cells that I am trying to sort shift down one row any time data in a separate part of the worksheet is entered. The code you sent me still gives me the same error because of the line .setrange range("a:T" & last row). If I pick a cell (i.e. "H9:T" & LastRow) I have no problems, but the first row of data will shift down to H10 when data is entered into the field above.

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a column that shifts down

    I think that I am just going to need to use tables to get this done

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sorting a column that shifts down

    What happens with the code below?

    Code:
    Sub Test2()
       Dim bcell As Range, acell As Range, LastRow As Long
        With Worksheets("Sheet1")
    
            Set bcell = .Cells.Find("LCPO RECALL", LookIn:=xlValues, lookat:=xlWhole)
            Set acell = bcell.Offset(1, 0)
            LastRow = .Cells(Rows.Count, 8).End(xlUp).Row
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("T:T") _
                                      , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange .Parent.Range(acell, Worksheets("Sheet1").Cells(LastRow, "T"))
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a column that shifts down

    Mark,

    You are a genius. Could you please explain how you fixed this?

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sorting a column that shifts down

    Basically I just made the .SetRange a real range, just being dumb it took me a while to realize that you hadn't left the number off the a in the range but rather you were trying to put the variable in the string.

    If the previous code works then the code below should (it is just a bit tidier).

    Code:
    Sub Test3()
       Dim bcell As Range, acell As Range, LastRow As Long
        With Worksheets("Sheet1")
    
            Set bcell = .Cells.Find("LCPO RECALL", LookIn:=xlValues, lookat:=xlWhole)
            Set acell = bcell.Offset(1, 0)
            LastRow = .Cells(Rows.Count, 8).End(xlUp).Row
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("T:T") _
                                      , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange .Parent.Range(acell, .Parent.Cells(LastRow, "T"))
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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