How to sort column data regardless of col length

  • Thread starter Thread starter JBW
  • Start date Start date
J

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
 
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
 
Thanks Mike, worked a treat

Mike H said:
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
Click to expand...
 
Back
Top