Results 1 to 3 of 3

Thread: Shuffle Data

  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Shuffle Data

    Hi Experts,

    The code below shuffles data each time a form is opened. It shuffles everything below the header.

    I am trying, unsuccessfully so far, to have the data shuffled beginning in row 3. In other words, I want to have two headers.

    There is probably an obvious solution that I missed.


    Option Explicit

    Public Sub Shuffle() 'RG-redid this code.

    Dim lCnt As Long
    Dim rRng As Range
    Dim lRngRows As Long
    Dim lCntr As Long

    Set rRng = Range("MyRange")
    lRngRows = rRng.Rows.Count

    For lCntr = 2 To lRngRows

    'Add a random value for sorting
    With Cells(lCntr, 4)
    .Formula = "=RAND()"
    .Value = .Value
    End With

    Next lCntr

    'Sort on random value
    With Sheet1.Sort
    .SortFields.Clear
    .SortFields.Add rRng.Columns(4), xlSortOnValues, xlAscending
    .SetRange rRng
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
    End With

    End Sub 'Shuffle



    Sub SetMyRange()

    Dim lLastrow As Long
    Dim zTestCol As String

    zTestCol = "a" '*** Letter of Column used to test for last row! ***

    lLastrow = Cells(Rows.Count, zTestCol).End(xlUp).Row

    ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:="=Sheet1!R1C1:R" _
    & Format(lLastrow, "#") & "C13" 'RB-"C13" is for the number of columns


    End Sub 'MyRange

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Change:
    For lCntr = 2 To lRngRows

    To:
    For lCntr = 3 To lRngRows

    ---------------------------------------------

    Change:
    .Header = xlYes

    To:
    .Header = xlNo

    ----------------------------------------------

    Change:
    ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:="=Sheet1!R1C1:R" _
    & Format(lLastrow, "#") & "C13" 'RB-"C13" is for the number of columns

    To:
    ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:="=Sheet1!R3C1:R" _
    & Format(lLastrow, "#") & "C13" 'RB-"C13" is for the number of columns

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-06-20)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud,

    As usual, your expertise got me on course. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •