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
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 )

     

     

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


      I found it c should be r

       

      This works perfectly

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

      ....

      this code works for line 2 and column G, targeting doubling of all lines, no matter how long they are.
      .for those who are looking at this, you need to read and change "A" to the appropriate column and (r) to the line where your data starts reading the comment above

      thank you

Resources