Certain Rows at Bottom

[1eric]

How can I get rows that if their cells have a certain value, place that row at the bottom of the table. For example, if my table has a column labeled Employee but the value in the cell is unknown, then i want that row to be placed at the bottom of the table.

 

[RudiS]

A quick way is to use a helper column.
In a new (helper) column in your list, create a formula: =IF(C2="Unknown","ZZZ",C2)

C2 represents the first cell in the column with the "Unknown" value. Replace C2 in the formula with the appropriate cell reference.
Autofill the formula down the column.
It will cause all "unknown" values to recalc to ZZZ
Now sort on this column in Ascending order. (It should force all unknowns to the bottom).

 

[1eric]

I should have added I would prefer a VBA code to do this so that I could create a button and attach the macro to it.

 

[1eric]

can anyone help with a macro to do this?

 

[RudiS]

I'll set one up for you......

 

[RudiS]

Try this macro...

Change the Constant Values at the top of the macro to determine which column (index number) to sort and what term of move to the bottom.

Sub SortToBottom()
Dim lR As Long
'===========================================================
'(Specify the column index number to sort EG: Column E = 5)
    Const SortColumn = 4
'(Specify the value to move to the bottom EG: "Unknown")
    Const ValToMove = "Unknown"
'===========================================================


    Application.ScreenUpdating = False
    lR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("A:A").Insert
    Range("A1:A" & lR).FormulaR1C1 = _
        "=IF(RC[" & SortColumn & "]=""" & ValToMove & """,""ZZZ"",RC[" & SortColumn & "])"
    Range("A1:A" & lR).Value = Range("A1:A" & lR).Value
    Range("A1").CurrentRegion.Sort Range("A1"), xlAscending, Header:=xlYes
    Columns("A:A").Delete Shift:=xlToLeft
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub