Auto Sort with Blank Formulas

[OX_2005]

I am trying to auto sort by Ascending order in the name column of this table. I currently have a formula set up in that column. The code I have written is working but it is putting the Blanks at the top (I am assuming its because they have formulas in them). Is there a way I can have it sort in Ascending order and leave the blanks at the bottom? (Current code below)

Sub SortRosterA()

Dim RA As Worksheet
Set RA = ThisWorkbook.Sheets("Shift Roster T-A 1 3")

'   Unprotect sheet

    RA.Unprotect
   
'   Not Allow screen to update

 Application.ScreenUpdating = False
 
'   Auto Sort

    With RA.ListObjects("DirectA").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("DirectA[Name]"), Order:=xlAscending
        .Apply
    End With



End Sub

 

[Domenic]

The following macro will add a temporary column, then it will sort based on the temporary column, and then it will delete the temporary column...

Sub SortRosterA()

'   Not Allow screen to update
    Application.ScreenUpdating = False
 
    Dim RA As Worksheet
    Set RA = ThisWorkbook.Sheets("Shift Roster T-A 1 3")

'   Unprotect sheet
    RA.Unprotect
 
    Dim tempListColumn As ListColumn
    Set tempListColumn = RA.ListObjects("DirectA").ListColumns.Add
   
    Dim sortListColumn As ListColumn
    Set sortListColumn = RA.ListObjects("DirectA").ListColumns("Name")
 
    Dim rowIndex As Long
    With sortListColumn.DataBodyRange
        For rowIndex = 1 To .Rows.Count
            If Len(.Cells(rowIndex)) = 0 Then
               tempListColumn.DataBodyRange.Cells(rowIndex).Value = "zzzzz"
            Else
                tempListColumn.DataBodyRange.Cells(rowIndex).Value = .Cells(rowIndex).Value
            End If
        Next rowIndex
    End With

'   Auto Sort
    With RA.ListObjects("DirectA").Sort
        .SortFields.Clear
        .SortFields.Add Key:=tempListColumn.Range, Order:=xlAscending
        .Apply
    End With
   
    tempListColumn.Delete
   
    Application.ScreenUpdating = True

End Sub

Hope this helps!

 

[OX_2005]

The following macro will add a temporary column, then it will sort based on the temporary column, and then it will delete the temporary column...

Sub SortRosterA()

'   Not Allow screen to update
    Application.ScreenUpdating = False
 
    Dim RA As Worksheet
    Set RA = ThisWorkbook.Sheets("Shift Roster T-A 1 3")

'   Unprotect sheet
    RA.Unprotect
 
    Dim tempListColumn As ListColumn
    Set tempListColumn = RA.ListObjects("DirectA").ListColumns.Add
  
    Dim sortListColumn As ListColumn
    Set sortListColumn = RA.ListObjects("DirectA").ListColumns("Name")
 
    Dim rowIndex As Long
    With sortListColumn.DataBodyRange
        For rowIndex = 1 To .Rows.Count
            If Len(.Cells(rowIndex)) = 0 Then
               tempListColumn.DataBodyRange.Cells(rowIndex).Value = "zzzzz"
            Else
                tempListColumn.DataBodyRange.Cells(rowIndex).Value = .Cells(rowIndex).Value
            End If
        Next rowIndex
    End With

'   Auto Sort
    With RA.ListObjects("DirectA").Sort
        .SortFields.Clear
        .SortFields.Add Key:=tempListColumn.Range, Order:=xlAscending
        .Apply
    End With
  
    tempListColumn.Delete
  
    Application.ScreenUpdating = True

End Sub

Hope this helps!

Will this remove any Formulas I have in place?

 

[Domenic]

No, your table will remain unchanged...

 

[OX_2005]

No, your table will remain unchanged...

I am getting an error and it might be because I multiple tables on the sheet

 

[OX_2005]

I forgot to add this is where the debug is coming in at

 

[Domenic]

When a new column is added to the table, it tries to insert cells to the right of it. However, in your case, it won't be able to do so, since it would mean that only part of Team 3 table would be moved, and you can't move part of a table.

One workaround would be to move your table (Team A Direct) to the right of your other tables. Woud that be acceptable to you?

 

[OX_2005]

When a new column is added to the table, it tries to insert cells to the right of it. However, in your case, it won't be able to do so, since it would mean that only part of Team 3 table would be moved, and you can't move part of a table.

One workaround would be to move your table (Team A Direct) to the right of your other tables. Woud that be acceptable to you?

That puts a damper in things. So the end game of this Macro is that when the button is pressed it will auto sort all tables on the page.

 

[Domenic]

Sure, it's understandable that you'd like to keep the layout unchanged.

Do these blank cells always occur at the end of the table before sorting? Or can they occur anywhere within the data? If the former, we can exclude those rows from the sort.

 

[OX_2005]

Sure, it's understandable that you'd like to keep the layout unchanged.

Do these blank cells always occur at the end of the table before sorting? Or can they occur anywhere within the data? If the former, we can exclude those rows from the sort.

The blank cells can sometimes be in the middle or at the end it depends on how many people we move around on shifts and then these get printed and thats how they take attendance