Sorting blank and non blank column of data

[Hari]

Hi,

I have a column of data in column AU where the data starts from row 3. Last
row which data in Column AU is calculated using VB and is designated as
rowcount.

Now the data is such that I could have Numeric and both alphanumeric data
and along with that some cells which are blank. I want to sort the data (
doesnt matter whether it is ascending or descending) such that the blanks
move to the bottom rows.

If I try sorting in Descending order then in this case blanks come between
the alphanumeric (top rows) and purely numbers ( bottom rows) . In case of
Ascending sort it comes between the pure numbers(top rows) and alphanumeric
( bottom rows).

Is there a way of sorting by which I may ensure that the sorting occurs such
that Blank rows all get lumped up in the bottom.

Why Im I doing this ? Because Im going to use the results of sorting and
feed them in as a named list for a validation box where the blank rows do
not figure in the validation box.( Using Debra Dalgleish's technique )

Please guide me.

I have pasted a sample of my data here.

123
116
356
345



546
124
2358
76tyjr
76jytt


7
869
346
ew


Regards,
Hari
India

 

[macropod]

Hi Hari,

The problem is most likely that your 'blank' cells aren't empty. They may
have space characters, various non-printing characters (eg char 160) or
nulls.

If you run a macro like the following over the data before sorting, you'll
get the results you're after:

Option Explicit
Dim SBar As Boolean

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim Percent As Integer
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count *
ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Replace What:=Chr(160), Replacement:=Chr(32)
Cell.Value = Application.Trim(Cell.Value)
I = I + 1
If Int(I / CellCount * 100 + 0.5) = Percent + 1 Then
Percent = Percent + 1
Application.StatusBar = Percent & "% Trimmed"
End If
Next Cell
MsgBox "Finished trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

Cheers