VBA剪切粘贴的数据范围
我正在尝试制作大量的遗传数据,在使用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.