首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将多个非相邻列复制到Array

问将多个非相邻列复制到Array
EN

Stack Overflow用户
提问于 2020-06-10 12:30:54
回答 3查看 958关注 0票数 2

我试图将多个不相邻(非连续) excel列复制到一个数组中,但它不起作用。下面是我试过的..。

代码语言:javascript
复制
    Public Function Test()    
        Dim sh As Worksheet: Set sh = Application.Sheets("MyWorksheet")
        Dim lr As Long: lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).row
        Dim r1 As Range: Set r1 = sh.Range("A1:A" & lr)
        Dim r2 As Range: Set r2 = sh.Range("C1:C" & lr)
        Dim rAll As Range: Set rAll = Union(r1, r2)
        'Dim arr() As Variant: arr = Application.Transpose(rAll) <-- Throws Type mismatch error
        'Dim arr As Variant: arr = Application.Transpose(rAll) <-- arr Value = Error 2015
        Dim arr() As Variant: arr = rAll.Value2 ' <-- Only the first column (col A) is loaded.
    End Function

任何帮助都是非常感谢的!

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2020-06-10 21:15:47

谢谢PEH,很好的解释让我找到了以下解决方案:

代码语言:javascript
复制
    Function Test()
       Dim sh as Worksheet : set sh = Sheets("MySheet")
       Dim lr as Long : lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).row
       Dim arr () as Variant
       Dim idx as Long

       ' Delete unwanted columns to ensure contiguous columns...
       sh.Columns("B:B").Delete

       ' Load Array
       arr = Sheet("MySheet").Range("A1:B" & lr).value2

       ' This allows speedy index finds... Note, index(arr, startrow, keycol) 
       ' Will need to use "On Error" to handle key not being found
       idx = WorksheetFunction.match("MyKey", WorksheetFunction.Index(arr, 0, 2), 0)

       ' And then fast processing through the array
       For idx = idx to lr
          if (arr(idx, 2) <> "MyKey") then exit for
          ' do some processing...
       Next idx
   End Function

再次感谢您!

票数 0
EN

Stack Overflow用户

发布于 2020-06-11 06:40:34

基于Application.Index() 函数的替代解决方案

只是为了好玩,一种可供选择的解决方案,甚至允许诉诸于列顺序的A,D,C

代码语言:javascript
复制
Sub ExampleCall()
'[0]define range
    With Sheet1                   ' reference the project's source sheet Code(Name), e.g. Sheet1
        Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dim rng As Range: Set rng = .Range("A1:D" & lr)
    End With
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'[1]get data in defined columns order A,C,D
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim data: data = RearrangeCols(rng, "A,D,C")
'[2]write to any target range
    Sheet2.Range("F1").Resize(UBound(data), UBound(data, 2)) = data
End Sub

上述主要过程调用的帮助函数

代码语言:javascript
复制
Function RearrangeCols(rng As Range, ByVal ColumnList As String)
'Purpose: return rearranged column values based on ColumnList, e.g. Columns A,C,D instead of A:D
'[a]assign data to variant array
    Dim v: v = rng
'[b]rearrange columns
    v = Application.Index(v, Evaluate("row(1:" & UBound(v) & ")"), GetColNums(ColumnList))    ' Array(1, 3, 4)
'[c]return rearranged array values as function result
    RearrangeCols = v
End Function

Function GetColNums(ByVal ColumnList As String, Optional ByVal Delim As String = ",") As Variant()
'Purpose: return array of column numbers based on argument ColumnList, e.g. "A,C,D" ~> Array(1, 3, 4)
'[a]create 1-dim array based on string argument ColumnList via splitting
    Dim cols: cols = Split(ColumnList, Delim)
'[b]get the column numbers
    ReDim tmp(0 To UBound(cols))
    Dim i: For i = 0 To UBound(tmp):  tmp(i) = Range(cols(i) & ":" & cols(i)).Column: Next
'[c]return function result
    GetColNums = tmp
End Function

进一步解决方案//编辑截止2020年-06-11

为了完整起见,我演示了一个基于数组(此处:data)的进一步解决方案,该数组使用了Application.Index()函数中相当未知的双零参数(参见2b节)。

代码语言:javascript
复制
   data = Application.Transpose(Application.Index(data, 0, 0))
代码语言:javascript
复制
Sub FurtherSolution()
'[0]define range
    With Sheet1                   ' reference the project's source sheet Code(Name), e.g. Sheet1
        Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dim rng As Range: Set rng = .Range("A1:D" & lr)
    End With
'[1]assign data to variant array
    Dim v: v = rng
'[2]rearrange columns
    'a) define "flat" 1-dim array with 1-dim column data A,C,D (omitting B!)
    Dim data
    data = Array(aCol(v, 1), aCol(v, 3), aCol(v, 4))
    '=====================
    'b) create 2-dim array
    '---------------------
    data = Application.Transpose(Application.Index(data, 0, 0))
'[3]write to any target range
    Sheet2.Range("F1").Resize(UBound(data), UBound(data, 2)) = data

End Sub
Function aCol(DataArr, ByVal colNo As Long) As Variant()
'Purpose: return entire column data as "flat" 1-dim array
With Application
    aCol = .Transpose(.Index(DataArr, 0, colNo))
End With
End Function

警告:这第二种方法对于更大的数据集似乎没有那么好的性能。

相关链路

Some pecularities of the Application.Index() function

票数 1
EN

Stack Overflow用户

发布于 2022-02-21 00:33:54

使用数组的想法是提高速度。移动和删除列,以及"for“循环减慢您的速度。

我正在寻找一种方法来加速我的程序,从12万到6万甚至更少。

拟议的解决方案将其速度降低到45万。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/62303637

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档