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:


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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top