HELP: How to sort with VBA code but skip the cells tat were returned as blank (EXCEL2010)

Hi,

I have a range of data A4:Z1000, header in row1, (row2 - row3 with titles that I do not want to touch at all), which I can sort using buttons for most of the columns EXCEPT column A. When I click button to sort column A with my code, it puts the blank cells returned in col A on the TOP first then the Ascending data below them. I do not want the blanks to be above, but below instead. I suspect the reason is because all other cols that have not been entered any data do not have this problem because they do not have a function. In col A, i have entered a function below, so I think even though the blanks appear blank, EXCEL is treating the "blanks" as zeros or a ghost data because of this function. But I need this function. This function in col A:
=IFERROR(IF(C5<>"",VLOOKUP(C5,'Dir - SEHK'!$A$2:$B$2000,2,FALSE),""),"check or upd Directory sheet")

where, 'Dir - SEHK' is another sheet the VLOOKUP derives the values to fill into col A.

How can my VBA code be tuned so as to automatically skip or put the sorted data where the "blanks" caused by col A ?
My code:
Sub sort_Name()
'
' sort_Name Macro
'

'
Range("A1:Z1000").Select
ActiveWorkbook.Worksheets("Register - SEHK").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Register - SEHK").Sort.SortFields.Add Key:=Range( _
"A4:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Register - SEHK").Sort
.SetRange Range("A4:Z1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A3").Select
End Sub


Help, help ..
Jason

Hi,

Try this:

Please Login or Register  to view this content.

I tried it on a small sample that had blanks from formula errors. It sorted the values based on column A, with the blanks coming at the bottom.

hi djdjdj,

tried your code, but sad to say no can do .. the blanks still precede the sorted Ascended rows. In fact, this time with your code, on pressing the button, even the header rows 1 to 3 all are dragged into the sort process, which didn't happen with my own code. any idea ?

i slightly tweaked your code, the headers are not touched now, but the sort still doesn't work. the sorted rows with data appear right at the bottom of the range. Everything else at the TOP before the sorted filled rows are the blanks..

Sub sort_Name()
'
' sort_Name Macro
'

'
Dim LastRow

With Worksheets("Register - SEHK")
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
LastRow = Application.WorksheetFunction.Max(LastCell.Row)
End With

Range("A5:Z" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending

Set LastRow = Nothing

Range("A1").Select

End Sub

Can you attach your sheet or a sample?