答案1
获取颜色(和其他字体特征)的最简单方法是执行一个Copy
过程。如果这太慢,我们可以研究其他选项。
我会建议
- 将原始数据复制到新的工作表(以保留原始数据)
- 确定最后一个固定列 - 在您的样本中,它是标有稀释:
- 在最后一个固定列 +1 之后,每隔一列插入一个新列,直到最后一个实际列
- 将每个数据集第二行中的信息复制到右边的一个单元格(现在为空的列)。
- 删除 A 列中所有空白的行
Option Explicit
Sub Interleave2()
Dim wsSrc As Worksheet, wsRes As Worksheet
Dim rSrc As Range, rRes As Range
Dim LastRow As Long, LastCol As Long
Dim LastFixedColumn As Long
Dim I As Long, J As Long, K As Long, L As Long
Set wsSrc = Worksheets("sheet1")
Set wsRes = Worksheets("sheet2")
With wsSrc
LastRow = .Cells.Find(what:="*", after:=.Cells(1, 1), _
LookIn:=xlFormulas, searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
LookIn:=xlFormulas, searchorder:=xlByColumns, _
searchdirection:=xlPrevious).Column
Set rSrc = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
LastFixedColumn = rSrc.Find(what:="Dilution:", after:=rSrc.Cells(1)).Column
Application.ScreenUpdating = False
wsRes.Cells.Clear
rSrc.Copy wsRes.Cells(1, 1)
For I = LastCol To LastFixedColumn + 2 Step -1
Cells(1, I).EntireColumn.Insert shift:=xlToRight
Next I
With wsRes
LastRow = .Cells.Find(what:="*", after:=.Cells(1, 1), _
LookIn:=xlFormulas, searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
LookIn:=xlFormulas, searchorder:=xlByColumns, _
searchdirection:=xlPrevious).Column
Set rRes = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
For I = 3 To rRes.Rows.Count Step 2
For J = LastFixedColumn + 1 To rRes.Columns.Count Step 2
rRes(I, J).Copy rRes(I - 1, J + 1)
Next J
Next I
With rRes
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
With .EntireColumn
.ColumnWidth = 255
.AutoFit
End With
.EntireRow.AutoFit
End With
Application.ScreenUpdating = True
End Sub