Forum Discussion

Copper Contributor

Aug 16, 2022

Solved

Excel duplicate line meny at the same time and change the signs in a series of doublings.pervasive

I have excel sheet with 500 lines of data how can i duplicate all lines so that there will be the same data in every two (1 and 2 the same 3 and 4 the same) so instead of 500 lines there are 1000 eve...

excel

HansVogelaar

Aug 16, 2022

Bjarkiwaage

Run this macro:

Sub Dup()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 1 Step -1
        Range("A" & r).EntireRow.Copy
        Range("A" & r + 1).EntireRow.Insert
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Bjarkiwaage

Copper Contributor

Aug 16, 2022

Tank you, it is easy when you know how.

this works perfectly.

Sub Dup()

Dim r As Long

Dim m As Long

Application.ScreenUpdating = False

m = Range("A" & Rows.Count).End(xlUp).Row

For r = m To 1 Step -1

Range("A" & r).EntireRow.Copy

Range("A" & r + 1).EntireRow.Insert

Next r

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

......

now I have column with 1000 duplicate numbers every 2 are the same no matter if they have a minus or not. how to make the upper the master and the next down oppsit .( master is minus then the duplicate below plus) and (the other direction if master is plus.)

HansVogelaar

MVP

Aug 16, 2022

Bjarkiwaage

If your data begin in row 1:

Sub Opposite()
    Const c = "A" ' column with data
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, c).End(xlUp).Row
    For r = 1 To m Step 2
        Cells(r + 1, c).Value = -Cells(r, c).Value
    Next r
    Application.ScreenUpdating = True
End Sub

If your data begin in row 2, change For r = 1 To ... to For r = 2 To ...