Prev: Need To Protect "Tab code"
Next: Alpha Numeric’s bunch together
From: DyingIsis on 31 Mar 2010 22:58
Hello -

So I'm a little less experienced than I thought.

Below is my table.
Item Rate 1 Rate 2
A 0.60% 0.30%
B 0.50% 0.40%
C 0.50% 0.70%
E 0.30% 0.30%
F 0.20% 0.50%


Below is my Macro:
Sub Ordering_Macro()
'
' Ordering_Macro Macro
'
'
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
Key:=Range _
("C2:C6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
Key:=Range _
("B2:B6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
..Header = xlYes
..MatchCase = False
..Orientation = xlTopToBottom
..SortMethod = xlPinYin
..Apply
End With
End Sub

I want to be able to apply this macro whether I have 30 records or 3,000
records. I always have the same 3 columns no matter how many records I have.

Any help would greatly be appreciated.

Thanks for your help in advance.

- DyingIsis
From: ozgrid.com on 31 Mar 2010 23:11
Dim rRange1 as Range
Set rRange1=Range(Cells(2,3),Cells(Rows.Count,3).End(XlUp))
rRange1.Sort................

Same for your other ranges.
http://www.ozgrid.com/VBA/ExcelRanges.htm


--
Regards
Dave Hawley
www.ozgrid.com

"DyingIsis" <DyingIsis(a)discussions.microsoft.com> wrote in message
news:458F8F9C-9016-48FE-A175-42C512D91A1F(a)microsoft.com...
> Hello -
>
> So I'm a little less experienced than I thought.
>
> Below is my table.
> Item Rate 1 Rate 2
> A 0.60% 0.30%
> B 0.50% 0.40%
> C 0.50% 0.70%
> E 0.30% 0.30%
> F 0.20% 0.50%
>
>
> Below is my Macro:
> Sub Ordering_Macro()
> '
> ' Ordering_Macro Macro
> '
> '
> ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
> ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
> Key:=Range _
> ("C2:C6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
> xlSortNormal
> ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
> Key:=Range _
> ("B2:B6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
> xlSortNormal
> With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
> .Header = xlYes
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
> End With
> End Sub
>
> I want to be able to apply this macro whether I have 30 records or 3,000
> records. I always have the same 3 columns no matter how many records I
> have.
>
> Any help would greatly be appreciated.
>
> Thanks for your help in advance.
>
> - DyingIsis

 | 
Pages: 1
Prev: Need To Protect "Tab code"
Next: Alpha Numeric’s bunch together