Sort Ascending for variable Report length

Hello,

I'm trying to sort a report using case number in Ascending order, this report varies in length and data begins in cell B2 down the whole report. It needs to include the fields on either side ie. expand the selection.

I've been using this with not much success : -

Please Login or Register  to view this content.

Thanks in advance for any help.

er.. what / how is report_length defined ?

presumably the below would work for you ?

Please Login or Register  to view this content.

the above assumes you have no data beneath the table being sorted.

be warned that UsedRange can give misleading results given possibility of "dirty cells"... also if you want to persist with your approach I would advise you set LastRow to be .UsedRange.Row + .UsedRange.Rows.Count -1 rather than .UsedRange.Rows.Count ... the latter is potentially flawed should your data say at some point going forward change and commence from A2:Ax rather than A1... eg, assume data in A2:A10... Last Row returns 9 using rows.count method thus your resulting range becomes A2:A9 as opposed to the correct range of A2:A10, using the revised approach would generate A2:A10 correctly.

EDIT: maybe better to use: ActiveSheet.Cells.SpecialCells(xlLastCell).Row
(if you don't use the method outlined in my earlier example of course)