Forum Discussion

Bjarkiwaage's avatar
Bjarkiwaage
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's avatar
    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
HansVogelaar's avatar
HansVogelaar
MVP
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's avatar
    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's avatar
      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 ...

      • Bjarkiwaage's avatar
        Bjarkiwaage
        Copper Contributor
        Aug 16, 2022

        HansVogelaar

        Data start in row 2 and colum "G" ( so "A" to "G" )

        I cant put in screen shot,,

        I get this error message in run of marco

        compile error: Invalid next control variable reference ..

        Next c  ( c is colored )

         

         

Resources