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

  • Thread starter Thread starter Dan
  • Start date Start date
D

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
 
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
 
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 -
Click to expand...

I appreciate the help Per. Thank you.

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 -
Click to expand...

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
 
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 -
Click to expand...

All is good now, thanks for the help Per.

Dan
 
Back
Top