Tip: Andre sprog er Google-oversat. Du kan besøge English version af dette link.
Log på
x
or
x
x
Tilmeld
x

or

Sådan kopieres rækker baseret på celleværdi i en kolonne?

Eksempelvis har jeg en række data, som indeholder en liste over tal i kolonne D, og ​​nu vil jeg duplikere hele rækkerne et antal gange baseret på de numeriske værdier i kolonne D for at få det følgende resultat. Hvordan kunne jeg kopiere rækkerne flere gange baseret på celleværdierne i Excel?

doc duplicere rækker af celle 1

Duplikerer rækker flere gange baseret på celleværdier med VBA-kode


pil blå højre boble Duplikerer rækker flere gange baseret på celleværdier med VBA-kode

For at kopiere og duplikere hele rækkerne flere gange baseret på celleværdierne, kan følgende VBA-kode hjælpe dig, gør det som følger:

1. Hold nede i ALT + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.

2. Klik indsatte > Moduler, og indsæt følgende kode i Moduler Vindue.

VBA-kode: Duplikerer rækker flere gange baseret på celleværdi:

Sub CopyData()
'Updateby Extendoffice 20160922
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
        VInSertNum = Cells(xRow, "D")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
           Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "D")).Select
           Selection.Insert Shift:=xlDown
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Loop
    Application.ScreenUpdating = False
End Sub

3. Tryk derefter på F5 nøglen til at køre denne kode er hele rækker blevet dupliceret flere gange baseret på celleværdien i kolonne D, som du har brug for.

Noter: I ovenstående kode er brevet A Indikerer startkolonnen i dit dataregment og brevet D Er kolonnebrevet, at du vil duplikere rækkerne baseret på. Venligst skift dem til dit behov.



Anbefalede produktivitetsværktøjer

Office-fanen

guld star1 Medbring praktiske faner til Excel og andre Office-software, ligesom Chrome, Firefox og nye Internet Explorer.

Kutools til Excel

guld star1 Fantastiske! Forøg din produktivitet i 5 minutter. Har ikke brug for særlige færdigheder, spar to timer hver dag!

guld star1 300 Nye funktioner til Excel, gør Excel meget nemt og kraftfuldt:

  • Fusionere celler / rækker / kolonner uden at miste data.
  • Kombiner og konsolidér flere ark og arbejdsbøger.
  • Sammenlign ranges, kopier flere rækker, konverter tekst til dato, enheds- og valutaomregning.
  • Tæl efter farver, Personsøgning Subtotaler, Avanceret Sort og Super Filter,
  • Mere Vælg / Indsæt / Slet / Tekst / Format / Link / Kommentar / Arbejdsbøger / Regneark Værktøjer ...

Skærmbillede af Kutools til Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Leah · 4 months ago
    Hello, this worked great. However, I have a report with 1000 entries and the code stopped duplicating around entry 480. Is there something that I can add so that it completes the action on the entire report?
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hello, Leah,
      I have tested the code in 2000 rows, and it works well.
      Could you send your worksheet to me for testing the code?
      My email address is skyyang@extendoffice.com
      Look forward to your reply!
      • To post as a guest, your comment is unpublished.
        Leah · 4 months ago
        Hello! I got it to work. It was an error on my side, the report had a few blank rows that were hidden that were causing the script to stop looping. It worked for my report with 8,000 rows! Thank youQ
  • To post as a guest, your comment is unpublished.
    Jackie · 8 months ago
    So I am using this code but I want it to search the entire document not just row 1 or whatever is indicated by xRow = 1. I am trying to put in the range 1:2000 but it is not working. How can I identify xRow = any row on the sheet that includes the information I identify in the code below?


    Dim xRow As Long
    Dim Value As Variant


    xRow = 1: 2000

    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
    Value = Cells(xRow, "D")
    Value2 = Cells(xRow, "A")
    If Not ((Value = "allegheny general") And IsNumeric(Value2 = G0202)) Then
    Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
    Range(Cells(xRow + 1, "A"), Cells(xRow + 1, "D")).Select
    Selection.Insert Shift:=xlDown
    xRow = xRow + 1
    End If
    xRow = xRow + 1
    Loop
    Application.ScreenUpdating = False
    End Sub
  • To post as a guest, your comment is unpublished.
    Hayley · 8 months ago
    Do you know what the code would be to duplicate the row just the once, based on if say cell d contains 'Yes' - I have been chasing similar code but for something that will duplicate a row based on a cell saying yes
  • To post as a guest, your comment is unpublished.
    Steve · 10 months ago
    I used the code above which works great but I need one more step after the row is pasted. I just cannot get it to properly work. I need it to put zero in column "N" in the row after it is pasted but keep the value in "N" in the original copied row.


    Sub CopyData()
    'Updateby Extendoffice 20160922
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
    VInSertNum = Cells(xRow, "J")
    If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
    Range(Cells(xRow, "A"), Cells(xRow, "AN")).Copy
    ' Cells(xRow, 14).Value = 0 this did all rows
    Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "AN")).Select
    'Cells(xRow, 14).Value = 0
    'this did all rows
    Selection.Insert Shift:=xlDown
    ' Cells(xRow, 14).Value = 0 this did the first row only
    xRow = xRow + VInSertNum - 1
    'Cells(xRow - 1, 14).Value = 0
    End If
    ' Cells(xRow - 1, 14).Value = 0
    xRow = xRow + 1
    ' Cells(xRow + 1, 14).Value = 0
    Loop
    'Cells(xRow, 14).Value = 0 this did no rows
    Application.ScreenUpdating = False
    End Sub
  • To post as a guest, your comment is unpublished.
    OSHRI · 1 years ago
    Hi,
    Someone knows hot convert this VBA code to Google Apps scripts (google sheets)?