Sorting a column that shifts down

[clarkp81]

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.

 

[clarkp81]

is this even possible to do?

 

[MARK858]

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

    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

 

[clarkp81]

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.

 

[MARK858]

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?

.SetRange Range("a:T" & LastRow)

You might need to explain in words exactly what you expect the code to do.

 

[clarkp81]

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.

 

[clarkp81]

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

 

[MARK858]

What happens with the code below?

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

 

[clarkp81]

Mark,

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

 

[MARK858]

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).

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