Previous Topic Next Topic

Duplicate rows based on cell value

Solved/Closed
Excel
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
 - Feb 3, 2014 at 01:31 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 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.
Related:

2 replies

Answer 1 / 2
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
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
1
fireburn
Posts
28
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.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
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
0
fireburn
Feb 27, 2014 at 09:14 AM
Hi,

It's me again, the code that you gave me is really helping me a lot, if you could spare your time again and add some modification in your code. Will it be possible that the last cell in "A" when I will execute your code, the first 2 character which I formatted to custom as 00000000 will increment by 1? Like for example, when I execute your code and assuming that A1 is 27022014 the last cell A3 for example , like if we input 3 in F1, it will give us 3 lines right? So the last cell, in A3 as a given exmple will be automatically increment to 28012014. Thank you for your time. Have a good one.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
Feb 27, 2014 at 10:53 AM
Hi Fireburn,

Great to read that the code is doing it's job.

The easiest way to do this is if the last cell is also the last used cell, meaning there is nothing below it. I'm assuming it is.
And why don't you format the dates as being a date? If we just add 1, then 31012014 would turn into 32012014, which can't be right.

If the last cell is actually the last cell and you formatted the dates as dates then the following will work: 
Range("A" & Rows.Count).End(xlUp).Value = Range("A" & Rows.Count).End(xlUp).Value + 1

Place this line above the "End Sub" line.

Let me know how this works out for you.

Best regards,
Trowa
0
fireburn
Feb 27, 2014 at 10:59 AM
Hi, that's fast. Thank you. yes there's nothing below it, and it is really a date entry, but they want us to format the date with leading zeroes because it will be save in csv format and upload it to them for calculation of the services that we rendered.
0

Similar discussions

Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
Copy rows to other sheets based on value in column
LeedMe -
vcoolio -

6 responses
Create new sheets based on Column A data.
josh07429 -
Jay -

4 responses
Excel VBA copy and paste loop with logic condition
Yogibearv -
Yogibearv -

6 responses
Auto insert 'x' rows depending on value 'y'
nephilim3uk -
rizvisa1 -

6 responses
Converting rows of data in notepad to columns
Need help -
Philgood_FR -

2 responses