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.
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], 1, Header:=1
.Clear
End With
V = Application.GetSaveAsFilename(, "Excel workbook,*.xlsx")
If V <> False Then ActiveWorkbook.SaveAs V, 51
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.
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.
Re: remove random row to new workbook
Far from your initial post !
Sorry for my poor English.
Could you help me , pleaseOriginally Posted by Marc L
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 …
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.
Last edited by 302; 04-30-2022 at 06:00 AM.
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 …
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.
vba Expert please help me.
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 ?
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?
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 …
I draw a picture , maybe it could make myself clearly. that is what I want. thanks a lot.
Last edited by 302; 05-01-2022 at 11:06 AM.
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 …
Sorry, please forget my question.
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 V, L&, R&
With Application
.DisplayAlerts = False
.ScreenUpdating = False
Workbooks.Add xlWBATWorksheet
With [A1].CurrentRegion.Rows
V = .Columns(1).Value
[K1] = V(1, 1)
For L = 2 To .Count
R = Application.RandBetween(L, .Count)
[K2] = V(R, 1)
V(R, 1) = V(L, 1)
.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 …
Thanks for your code, but It cause error, I don't know why.
Last edited by 302; 05-02-2022 at 12:24 AM.
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.
No, I used the same attachment. After run the code blank worksheet appeared.
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 …
Sorry , I don't understand. Could you post your excel file, please. I try many times but failed with code.
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.
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.
To keep the code as it is (if it works, why change) and have the action done just once:
in this part of code:
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.
Please Login or Register to view this content.
Best Regards,
Kaper
Thanks so much!!!!To keep the code as it is (if it works, why change) and have the action done just once:
in this part of code:
remove first and last line (for and next lines), and addPlease Login or Register to view this content.before second line, so it shall now look like:Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks