How To Create A VBA SORT that only Sorts a two Columns ONLY and Nothing Else

[RudeBoy]

I currently have numbers (Dollar Values) in Two Columns Z15:Z70 and AA15:AA70. Now I only want those to Column to sort Ascending but Nothing else in the Worksheet is to Move just those two Columns.. Is it Possible.. I started to build some code but no luck this is moving everything..

Sub Worksheet_Activate()
'
' Row Sort Macro
' Macro recorded 4/25/2011 by Rude Boy Chris
'
rowcount = ActiveSheet.UsedRange.Rows.Count
Range(Cells(15, 1), Cells(rowcount, 26)).Select

Selection.Sort Key1:=Range("Z15"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub

 

[wigi]

If your selection is too large, why not looking at how your selection is made in the code? Try:

Sub Worksheet_Activate()
    rowcount = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(15, 26), Cells(rowcount, 27)).Select
    Selection.Sort Key1:=Range("Z15"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Range("A1").Activate
End Sub

 

[RudeBoy]

Ok I plugged that in and the sheet looked like it tried to sort but the Column Numbers stayed the same and did not sort.. HELPpppppppppp

COL COL
Z AA
The Numbers start in Row 15

ENV ENV
Per TTL
Bowler Won
Amount By Team
241 964
234 936
249 996
227 908
217 868
252 1008
208 832
194 776
221 884
282 1128
219 876
290 1160
267 1068
317 1268
274 1096
298 1192
223 892
382 1528
213 852
352 1408
307 1228
236 944
215 860
255 1020
229 916
201 804
261 1044
0 0
211 844
225 900
198 792
244 976
264 1056
204 816
209 836
328 1312
258 1032
200 800
246 984
192 768
195 780
206 824
239 956
197 788
203 812
231 924
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0

 

[wigi]

For me this code works perfectly.

I put the numbers in columns Z and AA, starting from row 15.

I select a different sheet, go back to the sheet and it is sorted. The columns A to Y do not change (I have fixed entries in there, no formulas).

 

[RudeBoy]

I think it has to do with the fact the Numbers are being Brought over from another Row..

Cel Z15 has in it a Formula of =R15, it keeps going Z16 = R16 etc etc..

AA15 has the same thing =Q15 and AA16 has =Q16..

Okay I tested it by copying the values to Notepad and back in and yes that is why its not working.. Hmm is there away to have the sort use the VALUE in the Cell when its sorting?

Or when I do the =R15 is there a way to have it Saved as a Hard Value 241 etc?

Thanks for your help..

 

[wigi]

You might want to copy/paste the range of cells as values:

Sub Worksheet_Activate()
    RowCount = ActiveSheet.UsedRange.Rows.Count
    With Range(Cells(15, 26), Cells(RowCount, 27))
        .Value = .Value
    End With
End Sub

Try this on a copy of your file !

Wigi

 

[RudeBoy]

Thanks That Worked..