Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is a descending sort putting blank cells first? | Excel Worksheet Functions | |||
sort and blank cells problem | Excel Worksheet Functions | |||
Sort when I have blank cells | Excel Discussion (Misc queries) | |||
Apply bottom border only on filled cells, leaves blank cells without border? | Excel Programming | |||
sort blank cells first instead of last | Excel Programming |