VBA Sort table based on cell text

[Darlie247]

Hello everyone,

I'm still learning VBA and all it can do. And I have really appreciated the help I have received on this forum through the years.

My current question is coding for automatically sorting an excel table (as data is entered) when a cell from a specific column contains specific text. I have attached an image (my workplace does not allow for add-ons to be downloaded). I would like for the table to auto sort wherein the "Yes" rows are placed first at the top of the table. From what I am understanding is that it has to be written as a change event?

Any help is always appreciated

 

[LongToast]

Assume you have 8 column, put this on your Worksheet_Change event :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        Dim iLast As Long: iLast = Me.UsedRange.Rows.Count

        With Me
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("H1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .Sort.SetRange .Range("A1:H" & iLast)
            .Sort.Header = xlYes
            .Sort.Apply
        End With
    End If
End Sub

It will give "Yes" in the top and "No" in the bottom.

 

[Peter_SSs]

It appears that you are working with a formal Excel table (ListObject). In that case and assuming that the values in the "Reorder?" column are not the result of formulas then try (after editing the table name as required) ..

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Table1[Reorder?]")) Is Nothing Then
    Application.ScreenUpdating = False
    With ActiveSheet.ListObjects("Table1").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("Table1[[#All],[Reorder?]]"), SortOn:=xlSortOnValues, Order:=xlDescending
      .Header = xlYes
      .Orientation = xlTopToBottom
      .Apply
    End With
    Application.ScreenUpdating = True
  End If
End Sub

If the "Reorder?" column is populated by formulas then try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Table1")) Is Nothing Then
    Application.ScreenUpdating = False
    With ActiveSheet.ListObjects("Table1").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("Table1[[#All],[Reorder?]]"), SortOn:=xlSortOnValues, Order:=xlDescending
      .Header = xlYes
      .Orientation = xlTopToBottom
      .Apply
    End With
    Application.ScreenUpdating = True
  End If

 

[Darlie247]

Thanks to both of you! These both worked for me.

I appreciate @Peter_SSs that you included a code for formulas, I totally forgot that I had some in that column (I should have mentioned it originally! My bad )

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.