Solved Auto Sort Excel Sheet

April 7, 2016 at 09:52:53
Specs: Windows 7
I am looking for a code to auto sort in ascending order some division numbers in column H of my excel sheet. The information that needs to be sorted is located from B9 to H36. I do not want it to sort column A at all.

See More: Auto Sort Excel Sheet

Report •


#1
April 8, 2016 at 04:52:30
We need a little more information before we can offer a possible solution.

1 - In your first sentence you say "auto sort in ascending order some division numbers in column H." In your second sentence you say "The information that needs to be sorted is located from B9 to H36."

So which is it? Column H or B9:H36?

2 - What will be the trigger for this "auto sort"?

3 - What is in the sort range? You used the words "division numbers" which to me implies formulas. If that is the case, then I believe that the formulas will need to be replaced with the values before the range can be sorted.

Perhaps a small example of your data would help. If you are going to post any example data, please click on the following line and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 11, 2016 at 07:55:26
I need it to sort B9:H36 by column H. Trigger I would like to be whenever info in column H is changed. Division numbers is just a number (1 through 36), never a formula.

Report •

#3
April 11, 2016 at 08:42:10
✔ Best Answer
Right click the sheet tab for the sheet you want to do the sort in and choose
View Code. Paste this into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("H9:H36")) Is Nothing Then
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
              Key:=Range("H9:H36"), SortOn:=xlSortOnValues, _
              Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("B9:H36")
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 11, 2016 at 09:47:41
It is not doing anything... I did change the Sheet1 to LOI but still not working.

Report •

#5
April 11, 2016 at 11:12:30
Just to eliminate any sheet name issues, you can use the following version of the code in any sheet. The "Me" designation will always point to the Sheet whose module contains the code. Please remember to delete the other code for that Sheet's module or you will have a conflict.

That said, without seeing your data, it's hard for me to troubleshoot from afar. Here is what I did:

I put a series of numbers in H9:H36.
I put a bunch of random numbers and text strings in B9:G36.

When I make a change to any value in H9:H36, the entire range is sorted based on Column H.

If this code doesn't work, you will need to provide some detail as to the contents of your range, the process you are following and the results. In other words, the code does what is written to do, so if it doesn't sort your data, the issue is external to the code.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("H9:H36")) Is Nothing Then
     Me.Sort.SortFields.Clear
     Me.Sort.SortFields.Add _
              Key:=Range("H9:H36"), SortOn:=xlSortOnValues, _
              Order:=xlAscending, DataOption:=xlSortNormal
        With Me.Sort
          .SetRange Range("B9:H36")
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 11, 2016 at 11:39:16
It worked! Thank you... I think the issue with the first is that I had 2 worksheet changes in my code and I hadn't combined them into one. Once I did your code worked PERFECTLY!!!

Report •

#7
April 11, 2016 at 13:13:59
Actually, if you had 2 Worksheet_Change macros in the same sheet module, you would have received an error message:

      Compile Error: Ambiguous name detected: Worksheet_Change

In any case, I'm glad you got it working.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question