Skip Blanks in Excel Data sort.

  peug417 21:11 14 Aug 2006
Locked

Normally when you Data sort text or numerals in cells any blank cells are removed. I have cells which contain formula. ie =IF('PERIOD 1 + 2'!B13="","",'PERIOD 1 + 2'!B13)etc.
I have a macro which should datasort a column with names in. the last column with league positions stays static. The problem is the sort macro wont skip the blank formulae cells so the empty cells will be at places 1 -15 with the leader who should be in position 1 in position 16. The macro used is



Sub Sortbydriver()

Range("B10:D83").Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B10").Select
End Sub

Anyone know how to cure this annoying glitch

  VoG II 21:18 14 Aug 2006

Have you tried filtering first to remove blanks.

You do not need the Select statement - in fact Select is very very rarely necessary. Try

Range("B10:D83").Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Application.Goto Range := "B10", scroll :=True

  peug417 21:31 14 Aug 2006

Not sure on filters, Can use the autofilter function though can't see a method to skip blanks?

  peug417 21:45 14 Aug 2006

Ok i think i have sorted it by the following..
select the cells
data>filter>autofilter
select the arrow and choose custom.
then Name "Contains" "********"
this then hides all the blank cells and then I can sort the names..
Thanks mate

  peug417 21:52 14 Aug 2006

No that didn't work still got blank rows appearing at the top...

  VoG II 21:54 14 Aug 2006

That is how Excel's sort works. What result are you expecting?

  peug417 21:58 14 Aug 2006

I need all the names to appear in order 1-75 either in alphabetical col b or in order of points scored in col dand any further blank formulated cells at the bottom of the pile. The cells require the formula in case new names are added.

  VoG II 22:05 14 Aug 2006

Double sort - sort all the cells then sort the cells with walues, But why are you using VBA to do this?

  peug417 22:12 14 Aug 2006

is the sort done via the filter or just data sort? I am using VBA as i am preparing a sheet that someone only has to push a button to see te result.

  VoG II 22:15 14 Aug 2006

Filter then Sort. Always.

  peug417 22:29 14 Aug 2006

WIll update tommorrow evening with progress.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?