Macro for Sorting a Variable amount of Rows

[stepheng2383]

Hi All,

I'm looking to be able to create a macro that will sort ascending a variable amount of rows. The form, in order to get a picture of what it looks like, is comprised of a variable number of invoices, extracted into Excel ('03) from another system. Each invoice also contains a variable amount of rows.

Let's say the numbers I would like to sort ascending are in Column U and the macro will get to the first line in this grouping via cell referencing. In this case, let's say the first line is Row 7 and there are 5 rows in the first invoice. Therefore, using cell referencing, I would have to sort ascending the range of A1:A5 (without cell referencing, U7:U12).

My two questions are as follows:
1) How can I create this variable range (i.e.

ActiveCell.Range("A1:A5").Select

), when A5 is variable

2) How can I create the entire variable range of the area it will sort ascending with it, expanding the selection to the entire block I'm working with, which will also vary according to top and bottom rows, but not columns

I have screwed around with vRowBottom, vRowTop, vDiff=vRowBottom - vRowTop, and then referencing vDiff in the Range, but I'm not quite sure how to do this with sorting. If I was using a formula such as sum, it would look something like this:

Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

(just as an example).

Please let me know if anyone has any thoughts and if you have any questions or need me to provide any further information, just let me know.

[Leith Ross]

Hello stepheng2383,

You need to post a sample workbook. If you have confidential information, change it before posting the workbook. If you are unable to change the workbook then provide samples that are functionally equivalent to the original workbook. These sample should be in a workbook if you are using worksheet formulas. Neither you or any of us want to recopy formulas into cells.

[stepheng2383]

I have attached a sample workbook (Excel '03) for your information, to see exactly what I'm referring to. I have also added in a comment in the "Agreement #" column. If you have any follow-up questions, please let me know. Thanks.

[Leith Ross]

Hello stepheng2383,

I take the agreement number and invoice number are the same thing. So, you want the line items copied over to another worksheet in ascending order by invoice number, and the total lines are to be ignored?

[stepheng2383]

The agreement number and invoice number are the same. I'm looking to sort the lines in each invoice (i.e. the first group of seven lines with data, making up the first invoice) in place by agreement number.

[stepheng2383]

Still in complications attempting to resolve this. If anyone has any ideas or would like further explanation of what I'm attempting to do, please let me know. Anything and everything would be greatly appreciated. Thanks!