Split data into several rows of same data Solved/Closed

Question

Hello,
I would like to split a row of data into several rows of same data in MS Excel. For example, I have an item with standard packing size of 100pcs per box. My customer places an order for 1,000pcs. I would like to have this order item repeated 10 times in the worksheet with each row having 100pcs as the box quantity.

Thanks in advance for your help.



Configuration: Windows 7 / Internet Explorer 8.0

TrowaD · Accepted Answer

Hi Wliang,

First select the entire row you would like to see repeated.

Run the following code:

Sub RepeatRow()
Dim iRow As Integer
Dim x As Integer
Dim y As Integer

iRow = ActiveCell.Row
x = InputBox("Number of times you want to display the selected row:")
y = 1

Do
Rows(iRow).Copy
Selection.Insert Shift:=xlDown
y = y + 1
Loop Until y = x

Application.CutCopyMode = False

End Sub
Running the code will let you input the number of times you want to see the row repeated.

In your case select the row with 100pcs in it, run the code, input 10 and you will see that your one row turned into 10 rows.

Best regards,
Trowa

wliang · Answer

Hi Trowa,
Thanks for your help. It works as mentioned as I am new to this VBA thing.

I have another question. I have this set of data as followed:

Part #             Total Qty
A                      1,000
B                         200
C                         100

For part A, the quantity per pallet is 500, part B is 100 and part C is 50. I wanted to have Part A to be repeated to the number of pallets based on the total quantity divided by quantity per pallet. In case for Part A, 1000 divided by 500 equals to 2, which means Part A is repeated twice. Same goes for Part B and C.

Thanks in advance for your help.

Best regards,
wliang

wliang · Answer

Hi rizvisa1,

Thanks for your reply. Yes, I have a master sheet where I list the parts and the # of units on a pallet. Basically, my master sheet is in this form:

Part No.    -----       Total Qty     -----        #of units per pallet
A               -----       1,000          -----         500
B               -----          200          -----          100
C               -----          100          -----            50

Then, these data will be repeated in the number of rows according to the result of the division of total quantity and # of units per pallet in a new sheet. I have uploaded a sample file for your perusal at https://authentification.site/files/29107975/Packing_Exp.xls

Thank you in advance for your help.

Best regards,
wliang

wliang · Answer

Hi Trowa,

Thank you very much for your help. This is exactly what I am looking for! This indeed helps solved the last part of my reporting requirements. 

Once again, thank you.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Sorry to re-open this message list again. I have tried to modify a bit of the codes you have provided to suit into the reporting format required. However, I encountered some problems.

1. Loop does not end even though rows were completely repeated.

2. Some other blanks rows were repeated as well.

I have attached the file here: https://authentification.site/files/29124147/Packfile.xls

Once again, thanks in advance for your help.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Thanks once again for your kind help and patience in helping me with my questions.

My first question is based on the codes you provided, I have tried to modify them to include a condition where the quantity of pallet is less than zero or with decimal point. Unfortunately, the macro loops endlessly. Below is my codes and I guess I didn't get it right somewhere.

Sub RepeatRow()
Dim sRow As Integer
Dim lrow As Integer
Dim x As Integer
Dim y As Integer

sRow = Range("A8").Row

x = Cells(sRow, 6)
y = 1

If x > 1 Then
    Do
    x = Cells(sRow, 6)
    y = 1

        Do
        Rows(sRow).Copy
        Rows(sRow).Select
        Selection.Insert Shift:=xlDown
        y = y + 1
        Loop Until y = x

    lrow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
    sRow = sRow + x
    Loop Until sRow = lrow
End If

Application.CutCopyMode = False

End Sub


And my second question is if the data is referenced from another sheet, is this macro still applicable? For example, data in Column B to F and Column J to L are referencing to data in Sheet1. I have tried but the macro loops endlessly. I guess it's not possible. Please correct me if I am wrong.

Once again, thank you for your help.

Best regards,
wliang

wliang · Answer

Hi Trowa,

Thanks for your response.

1. I know it's not possible to have quantity of pallets less than zero. This will only happens when our customers order a loose shipment (not full pallet quantity). When it is less than zero, the line should not be repeated and remained as it is.

2. If the quantity of pallets have a decimal point, it should be considered as 1, i.e. if 4.3 or 4.9 then it should be repeated 5 times.

Sorry for uploading a zipped file. Here is the unzipped version: https://authentification.site/files/29206764/Packfile1.xls

Again, thanks for your kind help.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Sorry for my late response. Thanks for your help. I have went through the codes and it indeed working great!

I believe I am nearing the completion of the report. However, I still have a couple of questions that would need your advice.

1. When the rows are repeated, only data in Column A to C to be shown in the repeated rows. Column D to F should be left blank in the repeated rows.

2. Column J to L should be formularized. In this case, I believe a macro can be recorded to drag the formula from the first row. What if the numbers of rows to be formularized is different everytime, will the recorded macro still works?

3. Is it possible to delete some blank rows after the last row with data?

You can use the same file as previous one. Sorry for so many questions. Appreciate your help.

Thank you very much in advance.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Thank you very much for your help. The codes are working great! By the way, I have make some slight changes to the codes so that it suits into the reporting requirements.

Regarding my third question in the previous post where the blank rows to be deleted, I looked through some of the posts here regarding the deletion of blank rows but can only find the codes for deleting hidden rows.

I have modified some codes but it was not working as intended. 

Sub DeleteRows()
Dim iRow As Long

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False

With ActiveSheet.UsedRange
For iRow = .Row + .Rows.Count - 1 To .Row Step -1
If Rows(iRow) = "" Then Selection.EntireRow.Delete
Next iRow
End With

.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Also, I have recorded a macro for blank rows deletion but it will only delete the specific rows recorded in the macro everytime although the blank rows are different.

I have attached the file for your perusal. In the file, I would like to have all the blank rows after Row 47 to be deleted but the calculation in Row 60 should remained and moved up accordingly when the blank rows are deleted.

Here is the file https://authentification.site/files/29469575/DPS_Shipment_Packing_V2.3.xls

Once again, thank you very much for your time and patience in helping me with this.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Sorry for my late response.

I worked with my colleagues to use the file and find it very helpful. And based on the assistance and guidance you provided, I am trying out some changes to make the file looks better.

Once again, thank you very much for your time, patience and guidance extended to help me build up this report.

P.S. Should I re-open this message list if I have some other questions related to this or I will need to open a new message list? Thanks in advance for your advice.

Best regards,
wliang

wliang · Answer

Hi Trowa,

Sorry to bother you again. I would like to insert some rows (say 4 rows) in between Row 38 and Row 40. After inserting the rows, I would like to have the formula in Row 38 to be dragged down to the blank rows.

I have referred to the codes that you have provided to me earlier and managed to insert 4 rows but the formula was not dragged down to the blank rows.

I have listed below the codes I used for your advice.

Sub LastRow()
Dim sRow As Integer
Dim lRow As Integer
Dim x As Integer
Dim y As Integer

sRow = Range("A38").Row
x = 1
y = 1

Do
Rows(sRow).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
x = x + 1
Loop Until x = 4

Do
lRow = Range("A" & Rows.Count).End(xlUp).Offset(-2, 0).Row
Range("A38:P38").Select
Selection.AutoFill Destination:=Range("A38:P" & lRow), Type:=xlFillDefault
y = y + 1
Loop Until y = 3

End Sub

Once again, thank you in advance for your advice.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Thanks for your feedback.

Referring to Q1 and Q2, I would like to have it fixed that everytime the macro is run, it will insert 4 rows. At the moment, it shall always be after row 38 to insert the blank rows.

As for Q3 and Q4, I want to autofill the formula for the range used in the above code, i.e. fill the formula from A8:P8. The autofill shall fill until 2 rows before the end of the list as I have a row after that for summing up of the figures, i.e. the autofill shall not overwrite the row that I use for summing up.

I am trying to create a new layout for the customer packing list with reference to the packing sheet. You can refer to the attached file:
https://authentification.site/files/29680235/DPS_Shipment_Packing_V2.52.xls

Thanks in advance for your support.

Best regards,
wliang

wliang · Answer

Hi Trowa,
Sorry for the late reply. I presented report to my team and it worked fine. Your advice and support really helped solved my problems.

Thank you very much.

Best regards,
wliang

Split data into several rows of same data

13 responses

Similar discussions

Subscribe To Our Newsletter!