VBA如何粘贴几列的行？

This currently my code, based off of this answer: https://*.com/a/11633207.

这是我目前的代码，基于这个答案：https：//*.com/a/11633207。

My code currently pastes the copied code on the bottom of Sheet2 starting at column A. How can I make it so that it copies the row starting at column C?

我的代码目前将复制的代码粘贴在Sheet2的底部，从A列开始。如何创建它以便从C列开始复制行？

Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long
Dim lastRow As Long
Dim strSearch As String
Dim i As Integer

Set ws1 = Worksheets("Sheet1")

With ws1
    .AutoFilterMode = False
    lRow = .Range("J" & .Rows.Count).End(xlUp).Row

    With .Range("J1:J" & lRow)
        strSearch = "John"
        .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With


    Set ws2 = Worksheets("Sheet2")
    With ws2
        lastRow = ws2.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        copyFrom.Copy .Rows(lastRow + 1)

    End With

    .AutoFilterMode = False
End With

3 个解决方案

#1

You can write this code a lot better and there is no need to copy the entire row and paste it. This will limit you, however just to easily solve your problem replace this line:

您可以更好地编写此代码，无需复制整行并粘贴它。这将限制您，但只是为了轻松解决您的问题替换此行：

copyFrom.Copy .Rows(lastRow + 1)

with this:

有了这个：

 Set Rng = copyFrom.SpecialCells(xlCellTypeConstants)
 Rng.Copy .Cells(lastRow + 1, 3)

Note that 3 represents column C and you can actually change it to whatever column you want.

请注意，3代表C列，您实际上可以将其更改为您想要的任何列。

#2

A quick way to do this is to still copy/paste the entire rows, but to then delete the first 2 columns on the destination side's rows, shifting towards the left:

快速执行此操作的方法是仍然复制/粘贴整行，但要删除目标端行的前两列，向左移动：

copyFrom.Copy .Rows(lastRow + 1) 'As before.

'Edit:

'If you meant to pull the pasted data towards the left:
.Range(.Cells(lastRow + 1, 1), .Cells(lastRow + GetRowsInRange(copyFrom), 2)).Delete Shift:=XlDeleteShiftDirection.xlShiftToLeft

'If you meant to push the pasted data towards the right:
.Range(.Cells(lastRow + 1, 1), .Cells(lastRow + GetRowsInRange(copyFrom), 2)).Insert Shift:=XlInsertShiftDirection.xlShiftToRight

Edit: you have to compute the number of rows that were copied.

编辑：您必须计算复制的行数。

Public Function GetNumRowsInRange(ByVal prngFullRows As Excel.Range) As Long
    Dim result As Long
    Dim rngArea As Excel.Range

    For Each rngArea In prngFullRows.Areas
        result = result + rngArea.Rows.Count
    Next

    GetNumRowsInRange = result
End Function

#3

-1

Set offset to move to 3 column which is C:

设置偏移量以移动到3列，即C：

Set copyFrom = .Offset(1, 3).SpecialCells(xlCellTypeVisible).EntireRow

Please try if this works for you,

请试试这是否适合你，

#1