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

No	Code	Name	Day 1	Day 2	Day 3	Day 4	Day 5
1	001	Jen	50	25	10	5	1
2	002	Sean
3	003	Meg
4	004	Len	50	20	10	1	1

<tbody>
</tbody>

End Result

No	Code	Name	Day 1	Day 2	Day 3	Day 4	Day 5
2	002	Sean
3	003	Meg
1	001	Jen	50	25	10	5	1
4	004	Len	50	20	10	1	1

<tbody>
</tbody>

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