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

    Code to Use for Worksheet Where Data Changes Often

    Hi Experts,

    The code below shuffles rows. It works well.

    I am trying to figure a way to replace A2:K100 so the code will process just the active cells, those with data instead of a range of cells because data size changes frequently.

    I've been trying to use currentregion like this: Range("A1").CurrentRegion.Select


    So far I haven't been able to make it work.

    On second thought, The very best way would be to use last row code for columns A to K. That would be better than currentRegion.


    Any ideas.

    Thanks



    Public
    Sub Shuffle()

    Dim lCnt AsLong
    Dim rRng As Range

    Set rRng = Sheet1.Range("A2:K100")

    'Record which row it starts on
    With rRng.Columns(3)
    .Formula ="=ROW()"
    .Value =.Value
    EndWith

    Do
    'Add a random value for sorting
    With rRng.Columns(4)
    .Formula ="=RAND()"
    .Value =.Value
    EndWith

    'Sort on random value
    Sheet1
    .Sort.SortFields.Clear
    Sheet1
    .Sort.SortFields.Add rRng.Columns(4), xlSortOnValues, xlAscending
    With Sheet1.Sort
    .SetRange rRng.Offset(-1).Resize(rRng.Rows.Count +1)
    .Header = xlYes
    .MatchCase =False
    .Orientation = xlTopToBottom
    .Apply
    EndWith

    lCnt
    = lCnt +1
    'if any rows are the same as the starting row
    'do it again
    LoopUntil ShuffleComplete(rRng.Columns(3))Or lCnt >100

    Debug
    .Print lCnt

    EndSub

    PublicFunction ShuffleComplete(rRng As Range)AsBoolean

    Dim rCell As Range
    Dim bReturn AsBoolean

    bReturn
    =True

    ForEach rCell In rRng.Cells
    If rCell.Value = rCell.Row Then
    bReturn
    =False
    ExitFor
    EndIf
    Next rCell

    ShuffleComplete
    = bReturn

    EndFunction
    Last edited by Excelnewbie; 2016-03-08 at 16:05.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    My recommendation would be a dynamic range name that would automatically adjust whenever new data was poured into the worksheet.

    Code:
    Option Explicit
    
    Sub SetMyRange()
    
       Dim lLastrow As Long
       Dim zTestCol As String
       
       zTestCol = "e" '*** 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, "#") & "C11"
       
    End Sub  'MyRange
    You can call this from your existing code and then use Range("MyRange")... in your code, e.g.

    Code:
     '*** Instead of... ***
    .SetRange rRng.Offset(-1).Resize(rRng.Rows.Count +1)
    
    '*** Use ... ***
    .SetRange Range("MyRange")
    Of course the code could also be modified so that you could pass a different column letter along with the desired name to use it for multiple selections. If you can use this feature let me know and I'll adjust the code.

    HTH
    Last edited by RetiredGeek; 2016-03-08 at 16:51.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Attached is a file where I added your code. I am not doing something right?

    I hope to have the code trigger when the form is opened.

    Thanks for your help and patience.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2016-03-09 at 10:08.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Attached find a reworked version. I've made some major changes so I hope it still does what you want. I've given the file a new name so it won't overwrite your original and you can compare the coding.

    Observations:

    The use of the ME qualifier is redundant in a form module and just makes the code harder to read IMHO. Note: You do need it in the Unload statement though.

    Call is also redundant.

    Try to keep your code neatly indented as it makes the code much easier to read and understand the flow.

    I added an automatic exit when Next is pressed and there are no more questions. Just cleaner IMO.

    Revised file: WS-3-8-16-TorF-RGV1.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Thank you teacher! I appreciate everything you've done since I found this site.

    Your observations are greatly appreciated.

    I am learning VBA on my own as I have time. This site and Google are my resources. Google is helpful, but limited. This site is the best.
    Last edited by Excelnewbie; 2016-03-09 at 16:07.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I am trying to figure a way to replace A2:K100 so the code will process just the active cells
    Another possible solution to your original question

    Code:
    ActiveSheet.Cells.ClearFormats
    Set rRng = Sheet1.UsedRange
    Maud

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

    Excelnewbie (2016-03-14)

  8. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thanks for adding another solution. Much appreciated.

Posting Permissions

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