VBA Sorting problems

Question

Dearest Wizards,

I'm using a macro to move 'some' data (usually not all 104 rows) from one sheet to another then using the sort routine below.  What parameter can I change that will quit sorting blank cells to the top?

TIA for your help,

Sam

ActiveWorkbook.Worksheets("Mgr_Report").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Mgr_Report").Sort.SortFields.Add Key:=Range( _

        "C16:C119"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    With ActiveWorkbook.Worksheets("Mgr_Report").Sort

        .SetRange Range("A16:F119")

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

Answer 1

One of the possible way is to take help of a dummy helper column.

Assume that you have column-Z (which is right now blank and not used in your data).

In this column-Z put a formula such that wherever you have yr data in column-C, you put a word say "Data" or "SortThis"  and wherever you have a blank in column-C you put a "" in column-Z.

Now modify the code to sort your data on Column-Z in Descending order as key1 and Column-C in Ascending order as key2.

This will bring all the blank cells to the bottom.

At the end you can delete the column-Z.

Hope this Helps.