VBA Sort Till Last Blank Cell and Put Blanks on Top

[unknownymous]

Hello Gurus,

Can you possibly help me on the codes by how to sort below and put those blanks data (Day) on top and maybe put some light yellow highlight for easy reference?

Before

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]No[/TD]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001[/TD]
[TD]Jen[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]002[/TD]
[TD]Sean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]003[/TD]
[TD]Meg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]004[/TD]
[TD]Len[/TD]
[TD]50[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

End Result

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]No[/TD]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]002[/TD]
[TD]Sean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]003[/TD]
[TD]Meg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001[/TD]
[TD]Jen[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]004[/TD]
[TD]Len[/TD]
[TD]50[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Any help will be much appreciated.


Thank you.

 

[Mr Kulle]

if you start from cell A1

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1:H5").Select
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:H1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Mr Kulle]

now with color

Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1:H5").Select
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:H1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:H5").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub