How to sort column data regardless of col length

[JBW]

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

 

[Mike H]

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]

Thanks Mike, worked a treat

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