How do I ignore blanks when sorting in Excel?

April 29, 2009 at 03:56:25
Specs: Windows XP
Hi

I have attached a macro to a button to allow me to easily sort my data. The ascending sort works fine, but the descending sort lists all the blank cells first, when I want them to appear last. The blanks result from the formula =IF(ISNUMBER('All Share'!T612),('All Share'!T612),"") and I need to keep the formula in place. Can you help?


See More: How do I ignore blanks when sorting in Excel?

Report •


#1
April 29, 2009 at 05:02:41
How about adding some extra code to your macro to move the blank cells to the bottom of the list after the sort?

Use a loop to build the range of blank cells then do a cut/paste.

Or sort ascending then sort descending on just the cells with data.


Report •

#2
April 29, 2009 at 05:47:53
Would you be able to suggest how to do this please?

My attempt below did not work.

Sub Dividend_Yield_Descending()
'
Range("8:8", Range("B8").End(xlDown)).Sort Key1:=Range("G8"), Order1:=xlDescending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Range("G8").Select
Call Find_non_blank
End Sub

Sub Find_non_blank()
Curr_row = Selection.Row
Curr_col = Selection.Column
test = 0
Do While test = 0
If IsNumeric(Cells(Curr_row, Curr_col).Value) = True And Cells(Curr_row, Curr_col).Rows.Hidden = False Then
test = 1
Else: Curr_row = Curr_row + 1
End If
Loop
Cells(Curr_row, Curr_col).Select
End Sub


Report •

#3
April 29, 2009 at 08:16:27
Try this...

You know your spreadsheet better than I do, so you might have to play with the ranges a little, but I think the concept works. As always, you should test this on a backup copy of your spreadsheet.

Sub Dividend_Yield_Descending()
'
Range("8:8", Range("B8").End(xlDown)).Sort Key1:=Range("G8"), Order1:=xlDescending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Call MoveBlanks
End Sub

Sub MoveBlanks()
'
'Count rows that were sorted
 RowCount = Range("8:8", Range("B8").End(xlDown)).Rows.Count
'Loop through Column G range counting blanks
 For Each cell In Range("G8:G" & 8 + RowCount - 1)
  If cell <> "" Then GoTo countDone
   myBlanks = myBlanks + 1
 Next
countDone:
'If there are any blanks, Cut them and Insert above bottom of range
 If myBlanks > 0 Then
  Range(Cells(8, "G"), Cells(8 + myBlanks - 1, "G")).Cut
  Range("G" & 8 + RowCount).Insert
 End If
End Sub


Report •

Related Solutions

#4
April 29, 2009 at 08:50:07
Thanks for this.

I've tried to change your suggestion so that the cut doesn't just move the blank cells in the G column, but also the corresponding cells in the other columns in the range (B:AG), making them move together as they do during the sort. However changing the range from G to B:AG doesn't work.

I'd really appreciate it if you can point me in the right direction again.


Report •

#5
April 29, 2009 at 09:46:16
To move entire rows try this:

 If myBlanks > 0 Then
  Range(Cells(8, "G"), Cells(8 + myBlanks - 1, "G")).EntireRow.Cut
  Rows(8 + RowCount).Insert

To move just the range B8:AGxx try this:

 If myBlanks > 0 Then
  Range("B8:AG" & 8 + myBlanks - 1).Cut
  Range("B" & 8 + RowCount).Insert
 End If


Report •

#6
April 30, 2009 at 05:13:12
Is there a way that I can select the range without using the row and column numbers, rather than (B8:AG626)?

This is becuase I'm using another macro to replace the data set that the first macro is used on. The second data set is smaller, and despite the use of your suggested macro, sorting the cells by ascending order results in blank cells appearing at the top of the new list (the same number by which the first data set is bigger than the second).


Report •

#7
April 30, 2009 at 06:42:18
I'm confused by your question.

There are 3 common methods for working with a range in VBA:

1 - Using hardcoded strings e.g. Range ("B1:AG5")

2 - Calculating the range e.g.

Range ("B1:AG" & row_num)
Range(Cells(row_num1, col_num1), Cells(row_num2, col_num2))
Range(Cells(row_num1, "B"), Cells(row_num2, "AG"))

There are a number of different syntaxes that can be used for this, but the point is that you can build the range by using variables or values from a cell in a worksheet, etc.

3 - Using Named ranges, which can be static or dynamic.

Therefore when you ask Is there a way that I can select the range without using the row and column numbers, rather than (B8:AG626)? I'm not sure how to answer.

You say Is there a way that I can select the range without using the row and column numbers which tells me you don't want to use row and column numbers, but you also say rather than (B8:AG626) which also implies you don't want to hardcode the ranges. That's 2 negatives, isn't it?


Report •

#8
April 30, 2009 at 09:50:48
Thanks for all your help

I was a bit confused earlier, and now think that I can solve my problem in an easier way.

I need to insert the cut cells at the top of the list rather than paste them at the top, as the blank cells have not been shifting down and I therefore lose the formula in them.

I'm trying to use the macro below to insert the data rather than paste it, but simply replacing paste with insert does not work. Can you suggest a way to get round this?

If Range("C4") = "'BDP - SXXP'" Then
Range("B27:AG626").Select
Selection.Cut
Range("B8").Select
ActiveSheet.Paste


Report •

#9
April 30, 2009 at 11:58:28
Rarely do you need to Select a range to perform an operation on it. You'll notice that I never selected anything in any code I've suggested in this thread.

Not only does it slow the code down, it sometimes causes problems with the execuion of the code.

Try this instead:

If Range("C4") = "'BDP - SXXP'" Then
 Range("B27:AG626").Cut
 Range("B8").Insert
End If


Report •

#10
April 30, 2009 at 12:11:53
That has worked perfectly.

Thank you for all your help


Report •


Ask Question