Excel VB Sort Macro

[RandDUser]

I have the following macro that will sort range C3:H298 by column D -

Range("c3:h298").Select
Selection.Sort Key1:=Range("D"),Order1:=xlDescending, header:=xlNo, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

However, if someone adds a row, I don't want to change the range selection in the macro everytime. So I added a formula in cell A1 that will calculate the total rows. I want Cell A1 to now be in the range selection, but I'm not sure how to compile that. Here is what I tried -

Dim CELLA1
CELLA1 = Range("A1").Value
Range("c3:hCELLA1").Select
Selection.Sort Key1:=Range("D"), Order1:=xlDescending, header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Any help would be great. TIA.

 

[PWD]

Hi TIA. I'm not sure if I've got this right but I'll try to give something back to this forum. (I'm sure the purists would say this should have been submitted in the VBA forum, but I just stumbled across it - so, what the heck?)

I've found this useful to determine the last row of used data.

r = ActiveSheet.UsedRange.Rows.Count

This sort of thing seems to work in another circumstance:-

Range("C3:H" & r).Select

    Selection.Sort Key1:=Range("D3"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Hope this helps - and apologies to anyone else.

Des.

 

[SkipVought]

Hi,

If your table is contiguous, you do not need to select anything!

Just reference the top-loeft cell in the table and Excel will sort the CurrentRegion.

    Range("c3").Sort Key1:=Range("D3"),Order1:=xlDescending, header:=xlNo, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Skip,
[sub]
[red][/red]
[/sub]

 

[RandDUser]

Well I have columns on opposite sides that are populated, but do not need sorted, and I have 3 rows that are totals at the bottom that have to be excluded, so with that, I used:

r = ActiveSheet.UsedRange.Rows.Count - 3

And that worked perfectly. Thank you both!

 

[SkipVought]

Why do you have column of data contiguous to your table. That is not good table design.

Why do you have totals below your table where new rows of data need to get entered? Why make someone, anyone, scroll down thru your table to find the table aggregations? This is a hold-over from the old paper, pencil & adding machine days. Why not put your aggregations at the TOP OF THE SHEET, where they are easliy visible and obtained? Solves ALOT of problems.

Skip,
[sub]
[red][/red]
[/sub]