-1

I have a spreadsheet we are always updating with new data. I need to sort column "H" first and then column "G" second. They are numerical. I have tried using this code but get runtime error 1004. I am a newbie so please help.

Sub sort2columns()

 With Sheets("OP")
    LastRow = .Range("H" & .Rows.Count).End(xlUp).Row
    LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
 End With
 Range("H2:H" & LastRow).Sort key1:=Range("H2:H" & LastRow), order1:=xlAscending, Header:=xlNo
 Range("G2:G" & LastRow).Sort key2:=Range("G2:G" & LastRow), order2:=xlAscending, Header:=xlNo

     
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Tammy
  • 1
  • 1
  • 1
    You need to actually [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Jun 25 '20 at 17:10
  • I updated my code and get this error: Run-time error '1004" Sort method of Range class failed. Sub sort2columns() Dim LastRow As Long LastRow = Range("G2:H1000").End(xlDown).Row Range("H2:H" & LastRow).Sort key1:=Range("H2:H" & LastRow), order1:=xlAscending, Header:=xlNo Range("G2:G" & LastRow).Sort key2:=Range("G2:G" & LastRow), order1:=xlAscending, Header:=xlNo End Sub – Tammy Jun 25 '20 at 17:16
  • 1
    Look at the answers in the linked question not the question itself with code that doesn't run. – Warcupine Jun 25 '20 at 17:19
  • 1
    And [edit] your question instead of posting code in comments. – BigBen Jun 25 '20 at 17:19
  • Thank you for your patience with a newbie. I've updated my question. – Tammy Jun 25 '20 at 17:26
  • This error is NOT about finding the last row. It is about sorting the wrong way. The way it is done right now first tries to sort column "H", and then tries to sort column "G", but you only want to sort once... (normally i do want to do it once....) – Luuk Jun 25 '20 at 17:31
  • You're sorting the two columns independently - is that what you mean to do, or sort the whole dataset by those two columns? – Tim Williams Jun 25 '20 at 17:32
  • We are wanting to sort this list for where things are in a warehouse. "H" is the row in the warehouse and "G" is the section of the row. SO I want to sort "H" first then "G". – Tammy Jun 25 '20 at 17:36

1 Answers1

0

You immediately overwrite lastrow with lastrow, only need it once using whichever column has the most rows.

Put the end with at the bottom, since all these things are pertaining to the same sheet and add a . before range

If you want to sort two columns in the same dataset, use the keys parameter of the range.sort function.

Sub sort2columns()
 Dim LastRow as long
 With Sheets("OP")
    LastRow = .Range("G" & .Rows.Count).End(xlUp).Row

    .Range("G1:H" & LastRow).Sort key1:=.Range("H1"), order1:=xlAscending, key2:=.Range("G1"), order2:=xlAscending, Header:=xlYes
 End With

enter image description here

Warcupine
  • 4,460
  • 3
  • 15
  • 24
  • That worked but I think I should have clarified that I need to keep "H" and "G" needs to be grouped ascending based on "H". Example: Worker has a list of items to pull from "H" which is warehouse row 100. ANd needs to get the items from sections 15, 11, 25, 14. They want to be able to see the list by row 100 and in numerical order by section 11, 14, 15, 25 so they say steps to go pick the items. – Tammy Jun 25 '20 at 18:12
  • This does that, H gets sorted the primary sort with G after that, so all the H's are in order and any G that correlates to H is sorted. – Warcupine Jun 25 '20 at 18:29
  • THANK YOU ALL FOR YOUR PATIENCE AND HELP! – Tammy Jun 25 '20 at 18:41