If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting blank and non blank column of data
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 |
#2
|
|||
|
|||
Sorting blank and non blank column of data
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 "Hari" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.725 / Virus Database: 480 - Release Date: 19/07/2004 |
Thread Tools | |
Display Modes | |
|
|