How to use control shift down key and sort from low value to high value in excel vba 2010?

Question

Hi I am working with a lot of random column cells. I am trying to use this option in excel vba. I used macro recorder but not working when I use this on another sheet.

Sorting also not working. Any idea??

For more details please check the below code :

Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]/1000"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I5396")
Range("I1:I5396").Select
Range("J1").Select
ActiveCell.FormulaR1C1 = ""
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("347").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("347").Sort.SortFields.Add Key:=Range("J1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("347").Sort
    .SetRange Range("J1:J5396")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

`ActiveWorkbook.Worksheets("347")....` specifically refers to that one worksheet. You will need to change that to accommodate other worksheets. — David Zemens, Feb 24 '15 at 14:44
there is 20 sheets.. can i do a loop to perform the operation on each sheet ? i am not so good in vba excel. it would be helpful if you help me with the changes on the code — Aman Khan, Feb 24 '15 at 14:54
You want to run this entire code on each of 20 sheets? Starting with the very first line? — David Zemens, Feb 24 '15 at 14:56
not working... :( i tried with change the name of the worksheet — Aman Khan, Feb 24 '15 at 15:00
Same problem arise. it collect the whole I column rows... but i only need the rows which contain value. my all calculations are depending on that column. so i am getting wrong values — Aman Khan, Feb 24 '15 at 15:16
OK. You still didn't answer my question about whether you want to run this entire code on each of the 20 sheets. If yes, then see my answer below. If not, then please revise your Q to clarify (do not put clarification in the comments, revise the question instead). — David Zemens, Feb 24 '15 at 15:23

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

To perform this in a loop on all of your worksheets in the ActiveWorkbook, you can use a For Each loop.

I declare a variable ws to represent the worksheet loop, and another variable rng to represent the column I, and sortRange to represent the column J which is to be sorted.

Dim ws as Worksheet
Dim rng as Range
Dim sortRange as Range

For each ws in ActiveWorkbook.Worksheets
    'Simulate the ctrl+shift+down to assign this range
    With ws
        Set rng = .Range("I1", .Range("I1").End(xlDown))
    End With

    'Assign the sorted range is one column to the right:
    Set sortRange = rng.Offset(0, 1)

    'Fill the formula in Column I:
    rng.FormulaR1C1 = "=RC[-3]/1000"

    '"Paste" the values from column I into column J 
    sortRange.Value = rng.Value

    'Set the sort fields:
    With ws
        With .Sort.SortFields
            .Clear
            .Add Key:=sortRange.Cells(1,1), _
                SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption:=xlSortNormal
        End With
        .Sort.SetRange sortRange
        .Sort.Header = xlGuess
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
Next

I have also modified this code to avoid relying on the Activate and Select methods. HERE is a good primer on why these methods are often problematic for many users. The macro recorder simply records very specific and explicit user actions. This is fine for giving you a "staring point", but in order to make most macros re-usable, it's necessary to modify away from simply "simulated keystrokes" and program directly to the object model, using variables rather than Selection.

edited May 23 '17 at 11:43

Community

1
1

answered Feb 24 '15 at 15:21

David Zemens

53,033
11
81
130

hi i am getting compiler error on " .sort " {9th line before the last line} – Aman Khan Feb 24 '15 at 15:34
sorry, delete that one line. – David Zemens Feb 24 '15 at 15:39
I want to run on 1 sheet and by default other sheets will also perform the same operation. this process worked ___ i used the some line on my code "Dim ws As Worksheet For Each ws In Sheets ws.Activate" before code "Next ws" before end of the code. please help me to get my code compiling and showing a perfect result – Aman Khan Feb 24 '15 at 15:41
compile error on next line.. .SetRange sortRange – Aman Khan Feb 24 '15 at 15:42
Try the revision. You do **not** (and should not) need to use `ws.Activate`. – David Zemens Feb 24 '15 at 16:00
$ .Add Key:=sortRange.Cells(1,1), _ SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal $ compiler syntex error – Aman Khan Feb 24 '15 at 16:15
This code compiles and works. Please revise your question to include the code you're currently attempting to use, and I will try to identify where you have made mistake. – David Zemens Feb 24 '15 at 16:25

How to use control shift down key and sort from low value to high value in excel vba 2010?

1 Answers1