What would a user function look like that if select line, provide a numeric value and duplicate that line N times?
N of "1" would mean "one additional copy" or two duplicate rows; 2 for three resulting rows, etc., ...
I've got the keyboard shortcuts that helps but still gets old having to do this lots of times with a variable count.
Thanks...
dpb wrote on 23-12-20 :
What would a user function look like that if select line, provide a
numeric value and duplicate that line N times?
N of "1" would mean "one additional copy" or two duplicate rows; 2 for
three resulting rows, etc., ...
I've got the keyboard shortcuts that helps but still gets old having
to do this lots of times with a variable count.
Thanks...
The new duplicates lines must follow the original one or be added
down to the end of a range?
Bruno
On 12/24/2020 8:10 AM, Bruno Campanini wrote:
dpb wrote on 23-12-20 :
What would a user function look like that if select line, provide a
numeric value and duplicate that line N times?
N of "1" would mean "one additional copy" or two duplicate rows; 2
for three resulting rows, etc., ...
I've got the keyboard shortcuts that helps but still gets old having
to do this lots of times with a variable count.
Thanks...
The new duplicates lines must follow the original one or be added
down to the end of a range?
Bruno
Directly underneath the existing line, Bruno, yes, so they remain as a
group.
On 12/24/2020 8:38 AM, dpb wrote:
On 12/24/2020 8:10 AM, Bruno Campanini wrote:
dpb wrote on 23-12-20 :
What would a user function look like that if select line, provide a
numeric value and duplicate that line N times?
N of "1" would mean "one additional copy" or two duplicate rows; 2 for >>>> three resulting rows, etc., ...
I've got the keyboard shortcuts that helps but still gets old having to >>>> do this lots of times with a variable count.
Thanks...
The new duplicates lines must follow the original one or be added
down to the end of a range?
Bruno
Directly underneath the existing line, Bruno, yes, so they remain as a
group.
There's a formula "=ROW()" in one column--I need that for some other
external processing that do on the file. This ends up copying the value
of the existing row the three times instead of duplicating the formula
in the new rows.
Also, not sure how acts on your machine but it takes some considerable
time before it finishes and repaints the screen. It's not a huge
workbook, this one is now about 500 rows with about 30 columns.
Anything magic that can call to turn of updating or the like that might
make it be faster? It's slow enough now would probably just continue
the manual duplication.
Public Sub AddingDups()
Dim n As Integer, i As Integer, j As Integer
n = 3 ' How many duplicates to add
For j = 1 To n
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
For i = 1 To Selection.Columns.Count
Selection(1, i) = Selection(2, i)
Next
Next
End Sub
Although it dawned on me this would be a spot for macro -- and that does
work quite nicely. I recorded the above keyboard shortcut sequence with
a keyboard shortcut, so that Ctrl-Shift-P (for "Plus") dupes the line.
Then, Ctrl-Y can repeat the macro or repeat it again. Since it is rare
to have to do this more than two or three times (think four is most have
run into so far), that's not bad at all.
Hi,
Am Thu, 24 Dec 2020 14:20:18 -0600 schrieb dpb:
There's a formula "=ROW()" in one column--I need that for some other
external processing that do on the file. This ends up copying the value
of the existing row the three times instead of duplicating the formula
in the new rows.
Also, not sure how acts on your machine but it takes some considerable
time before it finishes and repaints the screen. It's not a huge
workbook, this one is now about 500 rows with about 30 columns.
Anything magic that can call to turn of updating or the like that might
make it be faster? It's slow enough now would probably just continue
the manual duplication.
try following code and run the code with right click in the expected
row:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim vardata As Variant
Dim cntCopies As Long
'Modify the columns
vardata = Range("A" & Target.Row & ":AD" & Target.Row)
'Modify the column with the number you want for the copies
cntCopies = Range("A" & Target.Row)
Rows(Target.Row + 1).Resize(cntCopies).Insert
Cells(Target.Row + 1, 1).Resize(cntCopies, UBound(vardata, 2)) = _
vardata
End Sub
Regards
Claus B.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 295 |
Nodes: | 16 (2 / 14) |
Uptime: | 20:43:49 |
Calls: | 6,640 |
Files: | 12,188 |
Messages: | 5,325,291 |