Ignore Blank Cells When Sorting

[Tubtech]

In the column I'm trying to sort, occasionally there are cells that contain a zero. Is there a way to omit them from the sorting macro and place them at the end. the macro I have written is as follows:

Sub SortRankings()

With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("AB7:AB26"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range("W7:AB26")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub


Ay help will be appreciated, Thanks!

[zbor]

Administrative Note:

We would very much like to help you with your query, however we recommend that you include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

[Artik]

If all the cells in column AB have numerical values (none are empty), you can remove 0(zero) from these cells before sorting, then sort the range and finally restore zeros in the empty cells. Review the following example:

Please Login or Register  to view this content.

Artik

[Tubtech]

Thanks I'll give it a try!