| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sort column data regardless of col length
HI,
I'm running a VBA macro and as part of it I need to sort a column of data, the column length changes week on week. I have found by chance on one week with 161 records that it only sorts 161 and leaves the other 4 alone and they appear at the bottom of the column unsorted. I did try changing the number to a large one ie 200 but it didnt like it at all? Sheets("Print Plan").Select Columns("B:B").Select Range("A1:J161").sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column data regardless of col length
Hi,
Assuming its the J161 bit that's going wrong and you really want to find the bottom cell used in column J try this lastrow = Range("J65536").End(xlUp).Row Range("A1:J" & lastrow).sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _ Mike "JBW" wrote: HI, I'm running a VBA macro and as part of it I need to sort a column of data, the column length changes week on week. I have found by chance on one week with 161 records that it only sorts 161 and leaves the other 4 alone and they appear at the bottom of the column unsorted. I did try changing the number to a large one ie 200 but it didnt like it at all? Sheets("Print Plan").Select Columns("B:B").Select Range("A1:J161").sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column data regardless of col length
Thanks Mike, worked a treat
"Mike H" wrote: Hi, Assuming its the J161 bit that's going wrong and you really want to find the bottom cell used in column J try this lastrow = Range("J65536").End(xlUp).Row Range("A1:J" & lastrow).sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _ Mike "JBW" wrote: HI, I'm running a VBA macro and as part of it I need to sort a column of data, the column length changes week on week. I have found by chance on one week with 161 records that it only sorts 161 and leaves the other 4 alone and they appear at the bottom of the column unsorted. I did try changing the number to a large one ie 200 but it didnt like it at all? Sheets("Print Plan").Select Columns("B:B").Select Range("A1:J161").sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
| Sort text list by character length - how? | Excel Worksheet Functions | |||
| How do I sort a large column of data by length? | Excel Worksheet Functions | |||
| How I type SOLD across column length without changing current data | Charts and Charting in Excel | |||
| How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
Linear Mode
