+ Reply to Thread
Results 1 to 2 of 2

Sorting including empty cells

  1. #1
    Registered User
    Join Date
    09-04-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    4

    Sorting including empty cells

    Hi,
    I am trying to sort names in column B. My problem is that all empty strings get sorted on top and I want them to be sorted on bottom.
    The sheet begins with empty cells and the the names comes after.

    Problem Details:
    in B14 I have the following formula:
    =TRIM(C14&" "&D14&" "&E14&" "&F14)
    Formila is copied until cell B64
    The sorting range is:
    Range("B14:FT64")

    Code:
    Private Sub CommandButton5_Click()
    'Sorting Names aphabatically

    With ActiveSheet
    .Unprotect Password:="1122"
    .Range("B14:FT64").Select

    Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom

    ActiveSheet.Protect "1122", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True
    .Range("C14").Select
    End With

    End Sub

    Any ideas?

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sorting including empty cells

    I think the cells are not empty (they only look empty).

    check the length of the cell with the formula =Len(b1)

    if the result is 0, the cell is empty.
    if the resutl is >0, the cell is filled.

    P.s. according to the forumrules, please add your code between tags. ##
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-04-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    4

    Re: Sorting including empty cells

    I tried the following:
    =len(b14) and the result was 0 (zero).

    the cells that looks empty are actually empty because the "len" gave the result of zero
    Any ideas?

  4. #4
    Registered User
    Join Date
    09-04-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    4

    Re: Sorting including empty cells

    I tried the following:
    =len(b14) and the result was 0 (zero).

    the cells that looks empty are actually empty because the "len" gave the result of zero
    Any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. only including non-empty cells in the formula
    By jveeken in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 04:43 AM
  2. [SOLVED] Pick Random Value from a Range (not including empty Cells)
    By ghoneim in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2013, 04:46 AM
  3. The rank of average ranks excluding empty cells but including their average.
    By Terminal45 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2012, 03:44 AM
  4. Excel 2007 : Sorting including empty cells
    By Domesticus in forum Excel General
    Replies: 0
    Last Post: 03-01-2011, 05:20 PM
  5. Sorting empty cells
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2010, 12:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1