Sorting a column that shifts down

clarkp81

New Member
Joined
May 3, 2019
Messages
13
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:

[HR][/HR]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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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:

clarkp81

New Member
Joined
May 3, 2019
Messages
13
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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:

clarkp81

New Member
Joined
May 3, 2019
Messages
13
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

New Member
Joined
May 3, 2019
Messages
13
I think that I am just going to need to use tables to get this done
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,151
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top