VBA中的随机行 [英] randomise rows in VBA

问题描述

所以我有一个excel文件与多个列和行。目前看起来像这样：

so i have an excel file with multiple columns and rows. At the moment it looks like this:

  | A  | B  | C  | D  
---------------------
1 | 1a | 1b | 1c | 1d 
---------------------
2 | 2a | 2b | 2c | 2d 
---------------------
3 | 3a | 3b | 3c | 3d
----------------------

我如何将其与VBA一起使用，使其成为：

How can i randomise it with VBA so that it becomes:

  | A  | B  | C  | D  
---------------------
1 | 3a | 3b | 3c | 3d 
---------------------
2 | 1a | 1b | 1c | 1d 
---------------------
3 | 2a | 2b | 2c | 2d
----------------------

推荐答案

这个问题有很多可能的答案。这可能是最跛脚的，但实际上它的效果相当不错：

It's true that this question has many possible answers. This is probably the most lame one, but it works quite ok actually:

1. 添加一个列;


2. 然后在此列中放入随机值;


3. 按此列排序 - 这正是您想要的！


4. 删除其他列，因此窍门是不可见的！


5. Voila！

1. Add an additional column;
2. Then put random value in this column;
3. Sort by this column - that's exactly what you want!
4. Delete the additional column, so the trick is no visible!
5. Voila!

只是给你一些想法应该看起来像：

Just to give you some idea how this should look like:

Option Explicit

Public Sub Randomize()

    Dim lCounter    As Long

    Application.ScreenUpdating = False
    Columns("A:A").Insert Shift:=xlToRight

    For lCounter = 1 To 5
        Cells(lCounter, 1) = Rnd()
    Next lCounter

    With ActiveSheet.Sort
        .SortFields.Add Key:=Range("A1:A5")
        .SetRange Range("A1:E5")
        .Apply
    End With

    Columns("A:A").Delete
    Application.ScreenUpdating = False

End Sub

它可以处理像这样的数据：

It would work on data like this one:

< a href =https://i.stack.imgur.com/fkSRh.png =nofollow noreferrer>

您可以进一步更新代码，删除魔术数字并提高范围。

You can further update the code, by removing the magic numbers and improving the ranges.

这篇关于VBA中的随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！