Results 1 to 4 of 4

Thread: Sort A to Z Ignore blanks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sort A to Z Ignore blanks

    this code below will Sort the Columns However, It puts the blank cells on top

    Code:
    Sub SortRampLog()    ActiveWorkbook.Worksheets("RAMP LOG (2)").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("RAMP LOG (2)").AutoFilter.Sort.SortFields.Add Key:= _
            Range("A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortNormal
        With ActiveWorkbook.Worksheets("RAMP LOG (2)").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    In row A2:a1000 I have the following code

    Code:
    =IFERROR(CONCATENATE(INDEX('EMPLOYEE LIST'!$D$2:$D$1000,MATCH(B3,'EMPLOYEE LIST'!$B$2:$B$1000,0)),", ",INDEX('EMPLOYEE LIST'!$C$2:$C$1000,MATCH(B3,'EMPLOYEE LIST'!$B$2:$B$1000,0)))," ")
    I have tried Putting "0" and "-" if error happens
    any suggestions is very appreciative

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,487
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sort A to Z Ignore blanks

    In the event of an error, your IFERROR formula is returning a space (" "). Try changing that to a zero-length string ("").
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort A to Z Ignore blanks

    Tried "" and still same results when i Sort A to Z Blanks at the top.

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort A to Z Ignore blanks

    I got it figured out. for anyone that would like to know just Remove the Iferror

    Code:
    CONCATENATE(INDEX('EMPLOYEE LIST'!$D$2:$D$1000,MATCH(B3,'EMPLOYEE LIST'!$B$2:$B$1000,0)),", ",INDEX('EMPLOYEE LIST'!$C$2:$C$1000,MATCH(B3,'EMPLOYEE LIST'!$B$2:$B$1000,0)))
    The cells will have "#N/A" but will not sort those cells with "N/A"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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