Skip Blanks in Excel Data sort.

  peug417 21:11 14 Aug 06
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 06

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 06

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

  peug417 21:45 14 Aug 06

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 06

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

  VoG II 21:54 14 Aug 06

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

  peug417 21:58 14 Aug 06

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 06

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 06

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 06

Filter then Sort. Always.

  peug417 22:29 14 Aug 06

WIll update tommorrow evening with progress.

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now