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

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

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

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

On 14 Dec., 02:31, Dan wrote:
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

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

On Dec 13, 6:52 pm, Per Jessen wrote:
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

On 14 Dec., 02:31, Dan wrote:



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- Hide quoted text -

- Show quoted text -

I appreciate the help Per. Thank you.

Dan

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

On Dec 13, 6:52 pm, Per Jessen wrote:
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

On 14 Dec., 02:31, Dan wrote:



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- Hide quoted text -

- 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

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

On Dec 13, 6:52 pm, Per Jessen wrote:
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

On 14 Dec., 02:31, Dan wrote:



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- Hide quoted text -

- Show quoted text -

All is good now, thanks for the help Per.

Dan