Would like to sort numbers in column smallest to largest

[MistakesWereMade]

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.

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

 

[MistakesWereMade]

It looks like this code actually works a lot better. I'm still getting a debug error though.

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


End Sub

 

[Fluff]

It should be

Range("C[COLOR=#ff0000]1[/COLOR]:C501")

 

[MistakesWereMade]

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?

Private Sub Worksheet_Calculate()


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


End Sub

 

[Fluff]

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]

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.

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

 

[Fluff]

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]

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.

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]

If you step through the code using F8 what happens?
Does it just restart at the beginning all the time?

 

[MistakesWereMade]

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.

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