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

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

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