Would like to sort numbers in column smallest to largest

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
I would like to sort cell values in a single column from smallest to largest. However, I can't seem to make my code work. I would like for my column to constantly sort the column when it is updated via its formula.

Below is my code. I made an If statement so that whenever the range of cells are updated/changed, it will trigger the sorting.

Code:
Dim Val As String


Val = Range("C2:C501").Value


Private Sub SortColumnC()


    If Range("C2:C501") <> Val Then
    
        Range("C2:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes


        Val = Range("C2:C501").Value


    End If


End Sub
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
It looks like this code actually works a lot better. I'm still getting a debug error though.

Code:
Private Sub Worksheet_Calculate()
    
        Range("C2:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,115
Office Version
  1. 365
Platform
  1. Windows
It should be
Code:
Range("C[COLOR=#ff0000]1[/COLOR]:C501")
 

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
Hi again Fluff! Thanks for helping. I made the change as shown below in my code. However, I'm still getting this error "Method 'Sort' of object 'Range' failed." My excel also crashes after exiting the debugging window...

Any ideas?

Code:
Private Sub Worksheet_Calculate()


Range("C1:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,115
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sounds like your sheet is constantly recalculating because of the sort & therefore you're in a permanent loop.
You maybe better of running the sort manually.
 

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
What if I make an If statement that checks to see if my condition is met and then will exit sub? I'm hoping that if I make SrcRange equal to my cell column, I can then run my if statement. Do you know how I can set my Dim SrcRange as a colum cell range variable? I keep getting errors for it... In Matlab, I am able to make a matrix/array. I want to do this for SrcRange and then to check itself after my sub notices a change in calculations.

I really do not want to have the user manually do everything when I could potentially program it to do everything automatically.

My error keeps saying "Invalid outside of procedure." as shown in the blue highlight.

Code:
Dim SrcRange() As Variant
SrcRange = Range("[COLOR=#0000cd]C2:C501[/COLOR]").Value

Private Sub Worksheet_Calculate()


    If Sheets("Formula Data").Range("C2:C501").Value <> SrcRange Then


        Range("C1:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes


        SrcRange = Sheets("Formula Data").Range("C2:C501")


        Exit Sub
    
    Else
    
        Exit Sub


End If


End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,115
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

There are times when manual is best, especially if it avoids a permanent loop. ;)

Even with what you are trying to do now, every-time any cell in sheet calculates, you are going to loop through 500 cells & check to see if they match. This will probably make the sheet very slow & unwieldy.
 

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
lol You are my excel God, Fluff. But what if I program it to cut the loop once, rather than at 500 reads? Say... like this for example.

Code:
Dim i As Integer


Private Sub Worksheet_Calculate()


For i = 1 To 1


    Range("C1:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
    Exit Sub
    
Next i


End Sub

My script runs and the cell range is sorted. It is right after this point though that it says "Method 'Sort' of object 'Range' failed." Is this too a loop issue? I do not understand the purpose of this error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,115
Office Version
  1. 365
Platform
  1. Windows
If you step through the code using F8 what happens?
Does it just restart at the beginning all the time?
 

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
I was able to get it working automatically using "application.enableevents" at the beginning and end of my code. Thought I would post my solution in case anyone is looking for a similar answer to their program.

Code:
Dim i As Integer


Private Sub Worksheet_Calculate()


Application.EnableEvents = False


For i = 1 To 1


    Range("C1:C501").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
    
Next i


Application.EnableEvents = True


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,678
Messages
5,524,241
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top