根据列中的值复制和插入行 [英] Copy and insert rows based off of values in a column

问题描述

我正在尝试设置一个在G列中查找单元格的过程，如果值大于1，则复制整个表行，插入一行（基于该值的次数为1），将该值粘贴到每个新插入的行中。

因此，如果单元格G4中的数量为3，那么我想复制该单元格的行并插入在下面一行2次并粘贴复制的值。

以下是我目前为止...

**注意所有这些都在Excel中的表中。 （不知道这是否是我的代码的问题）

  Dim Qty As Range 
 
 For Each数量范围（G：G）。单元格
如果Qty.Value> 1然后
 Qty.EntireRow.cell 
 Selection.Copy 
 ActiveCell.Offset（1）.EntireRow.Insert 
 Selection.Paste 
 Selection.Font.Strikethrough = True 
 
如果
 
下一个
 
 End Sub 
  

解决方案

您的方法和代码有一些问题

1. 你说数据在Excel表中。将其用于您的优势


2. 从下到上将行插入范围循环。这样可以防止插入的行干扰循环索引


3. 不要使用 Selection （即使你的逻辑不是操纵ActiveCell）


4. 不要循环遍列整列（百万行）。限于表格大小

以下是这些想法的演示

  Sub Demo（）
 Dim sh As Worksheet 
 Dim lo As ListObject 
 Dim rColumn As Range 
 Dim i As Long 
 Dim rws As Long 
 
设置sh = ActiveSheet'<  - 适合
设置lo = sh.ListObjects（YourColumnName）
 
设置rColumn = lo.ListColumns（YourColumnName）DataBodyRange 
 vTable = rColumn.Value 
 
对于i = rColumn.Rows.Count到1步-1 
如果rColumn.Cells（i，1）> 1然后
 rws = rColumn.Cells（i，1） -  1 
用rColumn.Rows（i）
 .Offset（1，0）.Resize（rws，1）.EntireRow。插入
 .EntireRow.Copy .Offset（1，0）.Resize（rws，1）.EntireRow 
 .Offset（1，0）.Resize（rws，1）.EntireRow.Font.Strikethrough = True 
 End with 
 End If 
 Next 
 End Sub 
  

I am trying to set up a procedure that looks up cells in Column "G" and if a value is greater than 1, copy that entire table row, insert a row (as many times - 1 based on the value) and paste that value into each newly inserted row.

So if there is a quantity of 3 in cell "G4" then I would like to copy the row of that cell and insert a row below it 2 times and paste the copied values.

Below is what I have so far...

**Note all of this is in a table in Excel. (not sure if that's part the issue with my code)

Dim Qty As Range

 For Each Qty In Range("G:G").cells
  If Qty.Value > 1 Then
   Qty.EntireRow.cell
   Selection.Copy
   ActiveCell.Offset(1).EntireRow.Insert
   Selection.Paste
   Selection.Font.Strikethrough = True

 End If

 Next

 End Sub

解决方案

There are a number of issues with your approach and code

1. You say the data is in an Excel Table. Use that to your advantage
2. When inserting rows into a range loop from the bottom up. This prevents the inserted rows interfering with the loop index
3. Don't use Selection (and even if you do your logic doesn't manipulate the ActiveCell)
4. Don't loop over the whole column (thats a million rows). Limit it to the table size

Here's a demonstration of these ideas

Sub Demo()
    Dim sh As Worksheet
    Dim lo As ListObject
    Dim rColumn As Range
    Dim i As Long
    Dim rws As Long

    Set sh = ActiveSheet ' <-- adjuct to suit
    Set lo = sh.ListObjects("YourColumnName")

    Set rColumn = lo.ListColumns("YourColumnName").DataBodyRange
    vTable = rColumn.Value

    For i = rColumn.Rows.Count To 1 Step -1
        If rColumn.Cells(i, 1) > 1 Then
            rws = rColumn.Cells(i, 1) - 1
            With rColumn.Rows(i)
                .Offset(1, 0).Resize(rws, 1).EntireRow.Insert
                .EntireRow.Copy .Offset(1, 0).Resize(rws, 1).EntireRow
                .Offset(1, 0).Resize(rws, 1).EntireRow.Font.Strikethrough = True
            End With
        End If
    Next
End Sub

这篇关于根据列中的值复制和插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！