Duplicate rows based on cell value

Solved/Closed

Excel

fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014 - Feb 3, 2014 at 01:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 13, 2014 at 12:07 PM

Hi,

Can somebody help me out with this. I want to duplicate rows based on input in a cell. can it be done in VB or macro or formula array? User will only input value in a cell (e.g. F1)

A B C D E F (eg F1 is the input cell)
1 2 3 4 5 2

and should look like this after inputting value in F1

A B C D E F
1 2 3 4 5 2
1 2 3 4 5
1 2 3 4 5

Thank you.

Excel duplicate rows based on cell value
Excel formula to duplicate rows based on cell value - Best answers
Excel macro duplicate row - Best answers
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Based on the value in cells b77 b81 ✓ - Excel Forum
Based on the cell values in cells b77 - Excel Forum
If cell contains date then return value ✓ - Office Software Forum
If a cell has text then return value ✓ - Excel Forum

2 responses

Answer 1 / 2

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 3, 2014 at 12:11 PM

Hi Fireburn,

Try this code and let me know how it works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer

If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Value = 0 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

x = Target.Value

Do
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy
    Range(Cells(Target.Row + 1, "A"), Cells(Target.Row + 1, "E")).Insert Shift:=xlDown
    x = x - 1
Loop Until x = 0

Application.CutCopyMode = False
End Sub

Best regards,
Trowa

fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014
Feb 13, 2014 at 09:43 AM

Hi,

Thank you so much for helping me out with the code it helped me a lot. I don't know so much about excel, I am a nurse working in senior's home. I am currently working on a spreadsheet. I have one more favor to ask you. I am working with data on a spreadsheet, if I enter the name of the client in a cell, I want to reference other data to table or database? wherein it will automatically enter other data on certain cells in the row like for example the postal code, room number etc. I don't know how to achieve it, i've searched a lot and can't find any answer.

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 13, 2014 at 10:52 AM

Hi Fireburn,

It sounds to me the VLOOKUP formula will do the job.

But without details I can't help you more specifically.

Could you provide sample data like you did in your original post as it is now and how you foresee the result?
Also consider the possibility of posting your workbook (careful with personal data) using a filesharing site like www.speedyshare.com or ge.tt and then post back the download link.

Best regards,
Trowa

Similar discussions

Copy and insert rows and number of times

Create new sheets based on Column A data.

Converting rows of data in notepad to columns

Compare values in two columns and return the value from third

Comparing columns and returning value

Excel - To generate unique ID

Similar discussions

Subscribe To Our Newsletter!