VBA to sort blank cells to bottom <--help please :o)

[Dan]

Hello -

I have the following code which, I think, will sort my spreadsheet
from row 5 down by column I.

Worksheets("JCW").Range("A4:M65536").Sort _
Key1:=Worksheets("JCW").Range("i5"), Order1:=xlDescending,
Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

2 problems...
1) All of the blank cells go to the top of the list, I would like
these on the bottom
2) I defined the entire workbook as a range, can the code be written
to only sort what is actually on the worksheet?

Thanks for any assistance,
Dan

 

[Per Jessen]

Hello Dan

Try this

With Worksheets("JCW")
Set LastCell = .Range("M65536").End(xlUp)

' Sort "ascending" to force empty cells to bottom
.Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlAscending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers

'Resize your data area, then sort descending
Set LastCell = .Range("M65536").End(xlUp)
Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlDescending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

Regards

Per

 

[Dan]

Hello Dan

Try this

With Worksheets("JCW")
Set LastCell = .Range("M65536").End(xlUp)

' Sort "ascending" to force empty cells to bottom
.Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlAscending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers

'Resize your data area, then sort descending
Set LastCell = .Range("M65536").End(xlUp)
Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlDescending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

Regards

Per







- Show quoted text -

I appreciate the help Per. Thank you.

Dan

 

[Dan]

Hello Dan

Try this

With Worksheets("JCW")
Set LastCell = .Range("M65536").End(xlUp)

' Sort "ascending" to force empty cells to bottom
.Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlAscending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers

'Resize your data area, then sort descending
Set LastCell = .Range("M65536").End(xlUp)
Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlDescending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

Regards

Per







- Show quoted text -

Per - I've tried this code and it sorts everything but still leaves
the blank cells on the bottom. Both of the sections of code you
provided seem to be the same, could this be the problem?

Thanks again,
Dan

 

[Dan]

Hello Dan

Try this

With Worksheets("JCW")
Set LastCell = .Range("M65536").End(xlUp)

' Sort "ascending" to force empty cells to bottom
.Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlAscending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers

'Resize your data area, then sort descending
Set LastCell = .Range("M65536").End(xlUp)
Range("A4", LastCell).Sort Key1:=.Range("i5"),
Order1:=xlDescending, Header:= _
xlYes, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

Regards

Per







- Show quoted text -

All is good now, thanks for the help Per.

Dan