+ Reply to Thread
Results 1 to 28 of 28

remove random row to new workbook

  1. #1
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Exclamation remove random row to new workbook

    Hello guys! How to set a random number according to the number of rows in a worksheet, randomly extract a non repeating row each time, and cut it from the original table to the new worksheet until all rows are extracted. And can be saved to the specified folder by folderpicker . please help me, thanks a lot.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Cool Hello, try this !


    According to your attachment an Excel basics VBA demonstration for starters :

    PHP Code: 
    Sub Demo1()
             
    Dim V
        With 
    [A1].CurrentRegion.Rows
             V 
    = .Count
            
    .Parent.Copy
            
    .Item("2:" V).Clear
        End With
        With Range
    ("B2:B" V)
            .
    Formula "=RAND()"
            
    .CurrentRegion.Sort [B1], 1Header:=1
            
    .Clear
        End With
            V 
    Application.GetSaveAsFilename(, "Excel workbook,*.xlsx")
            If 
    <> False Then ActiveWorkbook.SaveAs V51
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 04-30-2022 at 05:06 AM.

  3. #3
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: Hello, try this !

    Thanks for your reply, but that isn't what I want. I have 25 rows in my table. I need to automatically generate 25 workbooks. Each workbook is the content of randomly selected rows.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: remove random row to new workbook


    Far from your initial post !

  5. #5
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Sorry for my poor English.

  6. #6
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Quote Originally Posted by Marc L View Post

    Far from your initial post !
    Could you help me , please

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: remove random row to new workbook


    As your post #3 raises some questions so the better is you well elaborate this time each necessary step so without anything to guess

  8. #8
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    I uploaded new zip file, the source worksheet have 5 rows , and I want extract 5 workbook with random row from source worksheet. If the source worksheet have 50 rows then extract 50 workbooks.
    that is what I want.
    I am waiting for your new reply, thanks a lot.
    Attached Files Attached Files
    Last edited by 302; 04-30-2022 at 06:00 AM.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: remove random row to new workbook


    Is it really necessary to export each row randomly ?! As it's easier to export them per the original order

  10. #10
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Quote Originally Posted by Marc L View Post

    Is it really necessary to export each row randomly ?! As it's easier to export them per the original order
    Sorry for reply late. Yes , I need export each row randomly. IF the random number is 5, then export the 5th row to new workbook.

  11. #11
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    vba Expert please help me.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: remove random row to new workbook


    To help the explanation must be complete without anything to guess which is not the case here !
    Or are you enough confident with your Excel / VBA skills to complete any helper code
    in order to fit it to your need for what you forgot / misexplained ?

  13. #13
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    If Sheet1 has 10 rows of data, I need to randomly select a number from 1-10, cut the entire row which equal the random number in sheet1 export to a new workbook. I can repeat the operation until all the data in sheet1 are randomly selected. Can I make myself clear this time?

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: remove random row to new workbook


    So what should happen just after the first row is exported to a brand new workbook ?
    As I required to well elaborate each step
    Do it manually, take notes then once you understand your need it should be easy to describe it

  15. #15
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    I draw a picture , maybe it could make myself clearly. that is what I want. thanks a lot.
    Attached Images Attached Images
    Last edited by 302; 05-01-2022 at 11:06 AM.

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: remove random row to new workbook


    So you did not understand 'explain each step'

    Imagine you need to ask some programmable robot to bring the milk bottle to the dining room table :
    you just ask for 'bring the milk bottle' but you forgot to indicate
    to go to the kitchen
    to open the frigde door and after taking the bottle to close this door
    to move to the dining room
    where is the table

    The reason why I asked you to proceed manually and take notes then you should easily describe your need
    at least if you understand how Excel works

    So for the moment, after the random new order, once the first row is exported to a brand new workbook, what is the next step ?
    Last edited by Marc L; 05-01-2022 at 07:05 PM. Reason: typos

  17. #17
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Sorry, please forget my question.

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Cool Try this


    A VBA demonstration for a starting point (you may have to amend it for what you forgot / misexplained)
    to paste only to the source worksheet module :

    PHP Code: 
    Sub Demo1()
            
    Dim VL&, R&
        
    With Application
           
    .DisplayAlerts False
           
    .ScreenUpdating False
            Workbooks
    .Add xlWBATWorksheet
        With 
    [A1].CurrentRegion.Rows
            V 
    = .Columns(1).Value
            
    [K1] = V(11)
        For 
    2 To .Count
            R 
    Application.RandBetween(L, .Count)
            [
    K2] = V(R1)
            
    V(R1) = V(L1)
           .
    AdvancedFilter 2, [K1:K2], ActiveSheet.UsedRange.Rows(1)
            
    ActiveWorkbook.SaveAs ThisWorkbook.Path "\" & L - 1, 51
        Next
            Union(.Item("
    2:" & .Count), [K1:K2]).ClearContents
        End With
            ActiveWorkbook.Close
           .DisplayAlerts = True
           .ScreenUpdating = True
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 05-01-2022 at 11:11 PM. Reason: optimization

  19. #19
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: Try this

    Thanks for your code, but It cause error, I don't know why.
    Attached Images Attached Images
    Last edited by 302; 05-02-2022 at 12:24 AM.

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: remove random row to new workbook


    As it well works on my side so the bad is on yours !

    You should obviously try with the same attachment rather than with a blank worksheet
    Last edited by Marc L; 05-02-2022 at 12:28 AM.

  21. #21
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Quote Originally Posted by Marc L View Post

    As it well works on my side so the bad is on yours !

    You should obviously try with the same attachment rather than with a blank worksheet
    No, I used the same attachment. After run the code blank worksheet appeared.

  22. #22
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow As solution belongs to good enough readers


    Ok your bad 'cause you felt in the bad reader trap !

    The error occurs 'cause the procedure is not located where it must be as written in post #18

  23. #23
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: As solution belongs to good enough readers

    Quote Originally Posted by Marc L View Post

    Ok your bad 'cause you felt in the bad reader trap !

    The error occurs 'cause the procedure is not located where it must be as written in post #18
    Sorry , I don't understand. Could you post your excel file, please. I try many times but failed with code.

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Re: remove random row to new workbook


    Faster you just check where did you paste the VBA procedure !

    As it must be withn the Sheet1 worksheet module rather than a general / standard one

  25. #25
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Quote Originally Posted by Marc L View Post

    Faster you just check where did you paste the VBA procedure !

    As it must be withn the Sheet1 worksheet module rather than a general / standard one
    OK, I got it , you save my day sir! Thanks so much.

  26. #26
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Dear sir, could I ask another question, how to change this code to export only one file per run, that is, I don't need to cycle and export all the data at one time. I only want to execute the code once when needed, generating only one file at a time.

  27. #27
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,147

    Re: remove random row to new workbook

    To keep the code as it is (if it works, why change) and have the action done just once:
    in this part of code:
    Please Login or Register  to view this content.
    remove first and last line (for and next lines), and add
    Please Login or Register  to view this content.
    before second line, so it shall now look like:
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  28. #28
    Registered User
    Join Date
    12-27-2021
    Location
    China
    MS-Off Ver
    Ms office 2019
    Posts
    65

    Re: remove random row to new workbook

    Quote Originally Posted by Kaper View Post
    To keep the code as it is (if it works, why change) and have the action done just once:
    in this part of code:
    Please Login or Register  to view this content.
    remove first and last line (for and next lines), and add
    Please Login or Register  to view this content.
    before second line, so it shall now look like:
    Please Login or Register  to view this content.
    Thanks so much!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 05-13-2016, 08:31 AM
  2. Replies: 1
    Last Post: 05-13-2016, 05:01 AM
  3. Open random workbook,new workbook and copy some data.
    By nemo74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2013, 11:34 AM
  4. remove random rows
    By JakeMann in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-14-2013, 07:13 AM
  5. [SOLVED] Remove random customers and view impact on turnover and profit
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-01-2012, 04:16 PM
  6. Append Data From One Workbook to Another Workbook (Consolidate Random Columns)
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-23-2010, 01:15 PM
  7. remove random spaces in a text string
    By mariusescu in forum Excel General
    Replies: 2
    Last Post: 07-10-2008, 03:32 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1