Cookies幫助我們提供服務。 使用我們的服務即表示您同意我們使用cookies。
提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何根據列中的單元格值複製行?

例如,我有一個包含列D中的數字列表的數據范圍,現在,我想基於列D中的數值複製整個行多次以獲得以下結果。 我怎麼能根據Excel中的單元格值多次復制行?

doc通過單元格1複製行

使用VBA代碼根據單元格值多次重複行


箭頭藍色右泡 使用VBA代碼根據單元格值多次重複行

要根據單元格值多次復制和復制整個行,以下VBA代碼可能會幫助您,請按照以下方法操作:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到 模塊 窗口。

VBA代碼:根據單元格值多次重複行:

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。 然後按 F5 鍵來運行這個代碼,整個行根據D列中的單元格值被複製多次。

注意:在上面的代碼中,這個字母 A 表示數據范圍的起始列和字母 D 是要復制行的基礎上的列字母。 請改變他們到您的需要。



推薦的生產力工具

Office Tab

金星1 帶上方便的選項卡到Excel和其他Office軟件,就像Chrome,Firefox和新的Internet Explorer一樣。

Kutools for Excel

金星1 驚人! 提高您在5分鐘的生產力。 不需要任何特殊技能,每天保存兩個小時!

金星1 300 Excel的新功能,讓Excel變得簡單而強大:

  • 合併單元格/行/列而不丟失數據。
  • 合併和合併多個工作表和工作簿。
  • 比較範圍,複製多個範圍,將文本轉換為日期,單位和貨幣轉換。
  • 按顏色計算,分頁小計,高級分類和超級篩選,
  • 更多選擇/插入/刪除/文本/格式/鏈接/評論/工作簿/工作表工具...

Excel的Kutools屏幕截圖

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.
    KAS · 1 months ago
    Is there a way to update the module to only duplicate new data? I'm working on an ongoing document and do not want the code to duplicate data that has been previously duplicated.
  • To post as a guest, your comment is unpublished.
    Gerardo Enrique Heras Araujo · 2 months ago
    hi, for me is no working, I want to remove letters and number duplicate is possible?
  • To post as a guest, your comment is unpublished.
    Naomi · 2 months ago
    This script seems to be exactly what I need, however, when I run it I am getting an error on the line Selection.Insert Shift:=x1Down

    Any suggestions on how I fix this?
  • To post as a guest, your comment is unpublished.
    Manuel F · 2 months ago
    Thanks! it has been a great solution for all my troubles!
  • To post as a guest, your comment is unpublished.
    Leah · 3 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 · 3 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 · 3 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