VBA剪切粘贴的数据范围 [英] VBA cut-paste range of data

问题描述

我正在尝试制作大量的遗传数据，在使用VBA的Excel中更易读.我试图在第15列之后剪切并粘贴每7个有数据的单元格，然后将其放在第8-15列下.所包含的图片是我需要的示例.数据示例(RAW与我需要的样子)代码，实际数据要大一些.(680行，超过100列.)

I'm attempting to make a large amount of Genetic data, a little more legible in Excel using VBA. I am trying to cut and paste every 7 cells that have data, after the 15th column, and drop them down under columns 8-15. The example of what I need is in the picture included. DATA EXAMPLE (RAW vs What i need it to look like) As you can see by the code, the real data is a little bigger. (At 680 rows and over 100 columns.)

当我尝试运行代码时，将数据范围粘贴到新行中会失败.(错误1004)

When I try running the code, it is failing when it goes to paste the range of data into the new line. (error 1004)

我的代码是:

Sub ShiftRows()

    Dim codingCol, startCol As Integer

    Dim LastRow As Integer
    Dim CurrentRow As Integer
    Dim LastCol, BeginCol As Integer
    Dim CurrentInsertRow As Integer


    LastRow = 2

    For CurrentRow = 680 To LastRow Step -1
        LastCol = 15
        Do While Cells(CurrentRow, LastCol) <> ""
        LastCol = LastCol + 1
        Loop

    CurrentInsertRow = CurrentRow

    For BeginCol = 0 To ((LastCol - 15) / 7) - 1

        CurrentInsertRow = CurrentInsertRow + 1
        Rows(CurrentRow).Offset(1).Insert shift:=xlShiftDown
        Range(Cells(CurrentRow, 15 + (BeginCol * 7)).Address & ":" & Cells(CurrentRow, 15 + (BeginCol * 7) + 6).Address).Cut
        Range("H:N" & CurrentInsertRow).Paste

        Next BeginCol
    Next CurrentRow
End Sub

推荐答案

Range.Cut允许您在其后指定粘贴范围.尝试类似的东西:

Range.Cut allows you to specify the paste range after it. Try something like:

Range("A1:A3").Cut Range("B10")

在您将我的范围值替换为所需范围值的地方.

Where you substitute my range values for the ones you want.

这篇关于VBA剪切粘贴的数据范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！