엑셀 Q&A 게시판 - 오빠두엑셀

Sub CopyColumnsUsingArray()

    Dim wsSource As Worksheet ' 원본 시트
    Dim wsTarget As Worksheet ' 대상 시트
    Dim sourceData As Variant ' 원본 데이터를 담을 배열
    Dim targetData() As Variant ' 대상 데이터를 담을 배열
    Dim lastRow As Long       ' 데이터가 있는 마지막 행

    ' 시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet2")
    Set wsTarget = ThisWorkbook.Sheets("Sheet1")

    ' 데이터가 있는 마지막 행 찾기 (A열 기준)
    lastRow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row

    ' Sheet2의 A, Z, B, AV 열 데이터를 배열에 저장 (반드시 A열부터 순서대로)
    ' Range(Cells(1, "A"), Cells(lastRow, "A"))는 A열 전체를 의미
    ' Range(Cells(1, "Z"), Cells(lastRow, "Z"))는 Z열 전체를 의미
    ' A, Z, B, AV 순서로 데이터를 읽어 배열에 저장하기 위해 각각의 열을 읽고 하나의 배열에 합친다.
    sourceData = Application.WorksheetFunction.Transpose( _
        Union(wsSource.Range("A1:A" & lastRow), _
              wsSource.Range("Z1:Z" & lastRow), _
              wsSource.Range("B1:B" & lastRow), _
              wsSource.Range("AV1:AV" & lastRow)).Value _
    )

    ' 배열 크기 재설정 (원본 데이터의 총 행수와 동일하게)
    ReDim targetData(1 To lastRow, 1 To 4) ' 4개의 열을 위해 크기 4로 재설정

    ' 배열 데이터를 각 열에 맞게 재배치
    Dim r As Variant
    For r = 1 To lastRow
        targetData(r, 1) = sourceData(r, 1) ' A열 -> A열
        targetData(r, 2) = sourceData(r, 2) ' Z열 -> E열
        targetData(r, 3) = sourceData(r, 3) ' B열 -> C열
        targetData(r, 4) = sourceData(r, 4) ' AV열 -> D열
    Next r

    ' 대상 시트의 A, E, C, D 열에 배열 데이터 붙여넣기
    ' Sheet1의 A열부터 시작하여 A, E, C, D 열에 순서대로 붙여넣기
    wsTarget.Range("A1").Resize(lastRow, 4).Value = targetData

    ' 개체 해제
    Set wsSource = Nothing
    Set wsTarget = Nothing

End Sub

위는 [ 엑셀 sheet2의 a, z, b, av 열을 sheet1의 a, e, c, d 열로 복사하기를 배열로 처리 ] 하는 코드입니다. 배열로 해야 빠르다고 여기 선생님께서 알려주셨거든요. 그런데 vba상의 구문에러는 없이 컴파일은 되는데, 실행버튼을 만들어 누르면

'9' 런타임에러가 발생하였습니다. 아래 첨자 사용이 잘못되었습니다.

라는 에러가 나고 [ Debug ] 클릭하면

targetData(r, 1) = sourceData(r, 1) ' A열 -> A열 '<-- 이쪽으로 에러를 안내합니다.

질문 : 배열에 대해 위 에러를 인터넷에 찾아 검색해봤는데 첨자가 아니라 [범위초과: Subscript out of range ] 로 인한 에러같기도 하고, Ubound 를 해줘야 한다는 것 같기도 하고, 배열에 대해 잘 몰라 그러는데... 코드를 올바르게 수정해주시면 감사드리겠습니다.

sourceData = Application.WorksheetFunction.Transpose( _ Union(wsSource.Range("A1:A" & lastRow), _ wsSource.Range("Z1:Z" & lastRow), _ wsSource.Range("B1:B" & lastRow), _ wsSource.Range("AV1:AV" & lastRow)).Value _ ) '// 배열 차원 확인 (Immediate 창에 출력됨) Debug.Print "dim1:", LBound(sourceData, 1), UBound(sourceData, 1) Debug.Print "dim2:", LBound(sourceData, 2), UBound(sourceData, 2)

'// targetData는 [1..lastRow, 1..4] Dim r As Long For r = 1 To lastRow '// 매핑: A→A, Z→E, B→C, AV→D targetData(r, 1) = sourceData(1, r) '// A targetData(r, 2) = sourceData(2, r) '// Z targetData(r, 3) = sourceData(3, r) '// B targetData(r, 4) = sourceData(4, r) '// AV Next r

Dim r As Long For r = 1 To lastRow targetData(r, 1) = sourceData(r, 1) '// A targetData(r, 2) = sourceData(r, 2) '// Z targetData(r, 3) = sourceData(r, 3) '// B targetData(r, 4) = sourceData(r, 4) '// AV Next r

'// ---------------- 열별로 읽어서 바로 쓰기 ---------------- Dim aA As Variant, aZ As Variant, aB As Variant, aAV As Variant aA = wsSource.Range("A1:A" & lastRow).Value '// A aZ = wsSource.Range("Z1:Z" & lastRow).Value '// Z aB = wsSource.Range("B1:B" & lastRow).Value '// B aAV = wsSource.Range("AV1:AV" & lastRow).Value '// AV '// 매핑: A←A, E←Z, C←B, D←AV wsTarget.Range("A1").Resize(eRow, 1).Value = aA wsTarget.Range("E1").Resize(eRow, 1).Value = aZ wsTarget.Range("C1").Resize(eRow, 1).Value = aB wsTarget.Range("D1").Resize(eRow, 1).Value = aAV

Option Explicit Public Sub CopyColumnsUsingArray() '// ---------------- 퍼포먼스 ---------------- Dim scr As Boolean, calc As XlCalculation, evt As Boolean scr = Application.ScreenUpdating: Application.ScreenUpdating = False calc = Application.Calculation: Application.Calculation = xlCalculationManual evt = Application.EnableEvents: Application.EnableEvents = False On Error GoTo EH '// ---------------- 시트/마지막행 ---------------- Dim wsS As Worksheet, wsT As Worksheet Set wsS = ThisWorkbook.Worksheets("Sheet2") Set wsT = ThisWorkbook.Worksheets("Sheet1") Dim lastRow As Long lastRow = wsS.Cells(wsS.Rows.Count, "A").End(xlUp).Row If lastRow < 1 Then GoTo CleanExit '// ---------------- 원본 열을 각자 2D 배열로 수집 ---------------- Dim aA As Variant, aZ As Variant, aB As Variant, aAV As Variant aA = wsS.Range("A1:A" & lastRow).Value '// A aZ = wsS.Range("Z1:Z" & lastRow).Value '// Z aB = wsS.Range("B1:B" & lastRow).Value '// B aAV = wsS.Range("AV1:AV" & lastRow).Value '// AV '// ---------------- sourceData를 (행×4)로 구성 ---------------- Dim sourceData() As Variant ReDim sourceData(1 To lastRow, 1 To 4) Dim r As Long For r = 1 To lastRow sourceData(r, 1) = aA(r, 1) '// A sourceData(r, 2) = aZ(r, 1) '// Z sourceData(r, 3) = aB(r, 1) '// B sourceData(r, 4) = aAV(r, 1) '// AV Next r '// ---------------- 확인용 ---------------- Debug.Print "src dim1:", LBound(sourceData, 1), UBound(sourceData, 1) '// 1..lastRow Debug.Print "src dim2:", LBound(sourceData, 2), UBound(sourceData, 2) '// 1..4 '// ---------------- 사용 예: 대상에 열별로 쓰기 ---------------- '// 매핑: A←col1, E←col2, C←col3, D←col4 wsT.Range("A1:A" & lastRow).Value = Application.Index(sourceData, 0, 1)
wsT.Range("E1:E" & lastRow).Value = Application.Index(sourceData, 0, 2)
wsT.Range("C1:C" & lastRow).Value = Application.Index(sourceData, 0, 3)
wsT.Range("D1:D" & lastRow).Value = Application.Index(sourceData, 0, 4) CleanExit: Application.ScreenUpdating = scr Application.Calculation = calc Application.EnableEvents = evt Exit Sub EH: Debug.Print "CopyColumnsUsingArray:", Err.Number, Err.Description Resume CleanExit End Sub

원조백수 Lv.30

몇가지 문제가 있네요.
하나. UNION을 하면 여러 Area가 되는데 그걸 .Value나 .Value2로 배열로 바꾸면, 첫번째 Area만 변환될 겁니다.
여러 영역을 하나의 배열로 하려면 각각을 배열로 만들어 합치거나, 각 셀값을 돌면서 하나의 배열에 모아야 합니다.
둘. 2차원 배열이 하나의 열일 때, Transpose를 하면 2차원 배열이 아니라 1차원 배열로 변경됩니다.
즉 배열의 열이 몇 개 인지에 따라 1차원/2차원 배열로 달라지므로 꼭 확인을 하시거나,
아니면 별도로 열이 1개라도 2차원 배열로 변환하는 별도의 함수를 구현하시는 게 좋습니다.
아마도 이런 부분에서 원하는 배열의 index가 초과하여 발생하는 것 아닌가 싶습니다.

2025.09.28

GEEM Lv.10 작성자

@원조백수 님 안녕하세요. 엑셀을 체계적으로 공부하지못하고 어깨넘어로 배우다보니 배열의 효율성을 이제야 느껴서 또 이걸 인터넷을 뒤지며 어깨넘어로 공부하다보니 부족함이 많아 어디에서부터 질문해야할 지 막막합니다. 배열을 다루면 대개 Ubound, Transpose, Union, Resize 등이 따라나오더라구요. 배열을 필요에 따라 가공하는 방식같은데 꼬리에 꼬리를 무는 지식쌓기를 해야하니...배열이 그리 간단한 방식이 아니라는 생각이 듭니다. 어려운 만큼 구사하기가 어렵지만 고급스킬로 가는 길이라면 꼭 걸어가보고 싶습니다. 하나씨기 하나씩 이해하며 배우고 싶습니다. 선생님께서 답글달아주셔서 감사드리고 지금은 저 말씀이 무슨 뜻인지 사실 이해하지 못하고 있지만 나중에라도 다 찾아서 읽으며 이해하도록 하겠습니다. 거듭 감사드립니다.

파일 첨부

삭제됨

사용자가 삭제한 댓글입니다.

@마법의손 님 안녕하세요. 복사 또는 대입으로 하면 한행을 실행하는데 1초 정도 걸리고, 이 시간은 셀을 계산(복사)하는 과정이라기보다 대입(셀을 업데이트)하는 시간이 오래걸리는 것 같다는 느낌을 받습니다. 만약 10행을 해야한다면 동일동작을 10번 해야하니 10초가 걸릴 것입니다. 이를 개선하기 위하여 복사 또는 대입을 배열로 구현한다면, 우선 계산을 메모리에서 다 완성한다음에, 대입(셀을 업데이트)하는 동작을 마지막에 딱 1번만 실행하면 속도가 개선될 것이라는 생각이 들었습니다.

@마법의손 님 안녕하세요. 답글감사드립니다. 그런데위와 같이 하면 4번을 셀업데이트를 해야하는데, 저는 셀업데이트를 최종 마지막에 딱 한번만 하고 싶습니다. 그러기 위하여 4행 모두를 1개의 배열에 이걸 넣고 싶습니다.

채택된 답변

수메리안 Lv.24

안녕하세요~
일단 첨부파일이 있으면, 데이터를 보면 좋을텐데....
올려주신 코딩만으로 살펴보겠습니다.
1.

 lastRow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row

는

 lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

로 수정이 필요해 보입니다.
2. 현재 코딩에서, "'9' 런타임에러가 발생하였습니다. 아래 첨자 사용이 잘못되었습니다." 라는 에러메시지는

sourceData = WorksheetFunction.Transpose(Union(...).Value)

이렇게 받아온 sourceData의 차원 순서가 기대와 반대라서 그럴 수 있습니다.
Union(...).Value + Transpose 조합은 대체로 sourceData(1 To 4, 1 To lastRow) 형태(= 4행 × N열)로 잡히고,
루프에서는 sourceData(r, 1)처럼 r을 첫 번째 차원(=4 이하)에 넣고 있으니, r이 5가 되는 순간 “아래 첨자 사용 오류(9)”가 발생합니다.
아래와 같이 Debug를 추가해서 배열 구조를 확인해 보세요.

이렇게 해서 배열 구조가 (행 × 열)인지 (열 × 행)인지 확인한 다음, 루프 인덱스를 (r, c)로 쓸지 (c, r)로 쓸지 맞추면 됩니다.
즉, sourceData가 (4 × lastRow) 형태인지, (lastRow × 4) 형태인지를 구별하는 것입니다.
그래서, sourceData(1..4, 1..lastRow) 라면,

sourceData(1..lastRow, 1..4) 이면,

Union(...).Value + Transpose를 쓰면 대부분 (4 × N)으로 나오는데, 기존 코드의 sourceData(r, 1)가 에러를 일으킨 부분일 수 있습니다.
그러나 무엇보다도 위 댓글에서도 언급하셨듯이 비연속범위의 데이터구조에서는
Range("A1:A10,Z1:Z10") 같은 Union 범위를 .Value로 꺼내면, Excel은 하나의 2차원 배열로 묶어주지 않습니다.
대부분 첫 번째 Area만 배열로 반환하거나, 경우에 따라서는 아예 런타임 오류를 낼 수 있을 뿐만 아니라,
설령 Union으로 가져온 데이터가 배열로 만들어지더라도, 구조가 N행 × 1열일 수도 있고, 1행 × N열일 수도 있습니다.
여기에 WorksheetFunction.Transpose를 적용하면 단일 행/열인 경우엔 1차원 배열로 바뀌어버려서 차원 불일치 오류(아래 첨자 오류)를 일으킬 수 있습니다.
최종적으로, 데이터를 배열 수집하고자 한다면,
가장 빠르고 적합한 코딩으로 추천드리는 것은,

기존 코딩을 수정한다면,

3. wsTarget.Range("A1").Resize(lastRow, 4).Value = targetData 코드는 "연속된 범위(A:D 열)"에만 데이터를 채워 넣습니다.
하지만 요구사항은 A, E, C, D 열처럼 비연속적인 위치에 각각 값을 붙여 넣는 것이므로, 이 방식은 맞지 않습니다.
따라서 한 번에 처리할 수 없고, 열별로 따로 나누어 붙이는 방법을 써야 합니다~

안녕하세요~ 일단 첨부파일이 있으면, 데이터를 보면 좋을텐데.... 올려주신 코딩만으로 살펴보겠습니다. 1. <pre lang="vbnet" escaped="true"> lastRow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row</pre> 는 <pre lang="vbnet" escaped="true"> lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row</pre> 로 수정이 필요해 보입니다. 2. 현재 코딩에서, "'9' 런타임에러가 발생하였습니다. 아래 첨자 사용이 잘못되었습니다." 라는 에러메시지는 <pre lang="vbnet" escaped="true">sourceData = WorksheetFunction.Transpose(Union(...).Value)</pre> 이렇게 받아온 sourceData의 차원 순서가 기대와 반대라서 그럴 수 있습니다. Union(...).Value + Transpose 조합은 대체로 sourceData(1 To 4, 1 To lastRow) 형태(= 4행 × N열)로 잡히고, 루프에서는 sourceData(r, 1)처럼 r을 첫 번째 차원(=4 이하)에 넣고 있으니, r이 5가 되는 순간 “아래 첨자 사용 오류(9)”가 발생합니다. 아래와 같이 Debug를 추가해서 배열 구조를 확인해 보세요. <pre lang="vbnet" escaped="true">sourceData = Application.WorksheetFunction.Transpose( _
 Union(wsSource.Range("A1:A" & lastRow), _
 wsSource.Range("Z1:Z" & lastRow), _
 wsSource.Range("B1:B" & lastRow), _
 wsSource.Range("AV1:AV" & lastRow)).Value _
 )

'// 배열 차원 확인 (Immediate 창에 출력됨)
 Debug.Print "dim1:", LBound(sourceData, 1), UBound(sourceData, 1)
 Debug.Print "dim2:", LBound(sourceData, 2), UBound(sourceData, 2)</pre> 이렇게 해서 배열 구조가 (행 × 열)인지 (열 × 행)인지 확인한 다음, 루프 인덱스를 (r, c)로 쓸지 (c, r)로 쓸지 맞추면 됩니다. 즉, sourceData가 (4 × lastRow) 형태인지, (lastRow × 4) 형태인지를 구별하는 것입니다. 그래서, sourceData(1..4, 1..lastRow) 라면, <pre lang="vbnet" escaped="true">'// targetData는 [1..lastRow, 1..4]
Dim r As Long
For r = 1 To lastRow
 '// 매핑: A→A, Z→E, B→C, AV→D
 targetData(r, 1) = sourceData(1, r) '// A
 targetData(r, 2) = sourceData(2, r) '// Z
 targetData(r, 3) = sourceData(3, r) '// B
 targetData(r, 4) = sourceData(4, r) '// AV
Next r</pre> sourceData(1..lastRow, 1..4) 이면, <pre lang="vbnet" escaped="true">Dim r As Long
For r = 1 To lastRow
 targetData(r, 1) = sourceData(r, 1) '// A
 targetData(r, 2) = sourceData(r, 2) '// Z
 targetData(r, 3) = sourceData(r, 3) '// B
 targetData(r, 4) = sourceData(r, 4) '// AV
Next r</pre> Union(...).Value + Transpose를 쓰면 대부분 (4 × N)으로 나오는데, 기존 코드의 sourceData(r, 1)가 에러를 일으킨 부분일 수 있습니다. 그러나 무엇보다도 위 댓글에서도 언급하셨듯이 비연속범위의 데이터구조에서는 Range("A1:A10,Z1:Z10") 같은 Union 범위를 .Value로 꺼내면, Excel은 하나의 2차원 배열로 묶어주지 않습니다. 대부분 첫 번째 Area만 배열로 반환하거나, 경우에 따라서는 아예 런타임 오류를 낼 수 있을 뿐만 아니라, 설령 Union으로 가져온 데이터가 배열로 만들어지더라도, 구조가 N행 × 1열일 수도 있고, 1행 × N열일 수도 있습니다. 여기에 WorksheetFunction.Transpose를 적용하면 단일 행/열인 경우엔 1차원 배열로 바뀌어버려서 차원 불일치 오류(아래 첨자 오류)를 일으킬 수 있습니다. 최종적으로, 데이터를 배열 수집하고자 한다면, 가장 빠르고 적합한 코딩으로 추천드리는 것은, <pre lang="vbnet" escaped="true">'// ---------------- 열별로 읽어서 바로 쓰기 ----------------
 Dim aA As Variant, aZ As Variant, aB As Variant, aAV As Variant
 aA = wsSource.Range("A1:A" & lastRow).Value '// A
 aZ = wsSource.Range("Z1:Z" & lastRow).Value '// Z
 aB = wsSource.Range("B1:B" & lastRow).Value '// B
 aAV = wsSource.Range("AV1:AV" & lastRow).Value '// AV

'// 매핑: A←A, E←Z, C←B, D←AV
 wsTarget.Range("A1").Resize(eRow, 1).Value = aA
 wsTarget.Range("E1").Resize(eRow, 1).Value = aZ
 wsTarget.Range("C1").Resize(eRow, 1).Value = aB
 wsTarget.Range("D1").Resize(eRow, 1).Value = aAV</pre> 기존 코딩을 수정한다면, <pre lang="vbnet" escaped="true">Option Explicit

Public Sub CopyColumnsUsingArray()
 '// ---------------- 퍼포먼스 ----------------
 Dim scr As Boolean, calc As XlCalculation, evt As Boolean
 scr = Application.ScreenUpdating: Application.ScreenUpdating = False
 calc = Application.Calculation: Application.Calculation = xlCalculationManual
 evt = Application.EnableEvents: Application.EnableEvents = False
 
 On Error GoTo EH
 
 '// ---------------- 시트/마지막행 ----------------
 Dim wsS As Worksheet, wsT As Worksheet
 Set wsS = ThisWorkbook.Worksheets("Sheet2")
 Set wsT = ThisWorkbook.Worksheets("Sheet1")
 
 Dim lastRow As Long
 lastRow = wsS.Cells(wsS.Rows.Count, "A").End(xlUp).Row
 If lastRow < 1 Then GoTo CleanExit
 
 '// ---------------- 원본 열을 각자 2D 배열로 수집 ----------------
 Dim aA As Variant, aZ As Variant, aB As Variant, aAV As Variant
 aA = wsS.Range("A1:A" & lastRow).Value '// A
 aZ = wsS.Range("Z1:Z" & lastRow).Value '// Z
 aB = wsS.Range("B1:B" & lastRow).Value '// B
 aAV = wsS.Range("AV1:AV" & lastRow).Value '// AV
 
 '// ---------------- sourceData를 (행×4)로 구성 ----------------
 Dim sourceData() As Variant
 ReDim sourceData(1 To lastRow, 1 To 4)
 
 Dim r As Long
 For r = 1 To lastRow
 sourceData(r, 1) = aA(r, 1) '// A
 sourceData(r, 2) = aZ(r, 1) '// Z
 sourceData(r, 3) = aB(r, 1) '// B
 sourceData(r, 4) = aAV(r, 1) '// AV
 Next r
 
 '// ---------------- 확인용 ----------------
 Debug.Print "src dim1:", LBound(sourceData, 1), UBound(sourceData, 1) '// 1..lastRow
 Debug.Print "src dim2:", LBound(sourceData, 2), UBound(sourceData, 2) '// 1..4
 
 '// ---------------- 사용 예: 대상에 열별로 쓰기 ----------------
 '// 매핑: A←col1, E←col2, C←col3, D←col4
 wsT.Range("A1:A" & lastRow).Value = Application.Index(sourceData, 0, 1) wsT.Range("E1:E" & lastRow).Value = Application.Index(sourceData, 0, 2) wsT.Range("C1:C" & lastRow).Value = Application.Index(sourceData, 0, 3) wsT.Range("D1:D" & lastRow).Value = Application.Index(sourceData, 0, 4)

CleanExit:
 Application.ScreenUpdating = scr
 Application.Calculation = calc
 Application.EnableEvents = evt
 Exit Sub
EH:
 Debug.Print "CopyColumnsUsingArray:", Err.Number, Err.Description
 Resume CleanExit
End Sub</pre> 3. wsTarget.Range("A1").Resize(lastRow, 4).Value = targetData 코드는 "연속된 범위(A:D 열)"에만 데이터를 채워 넣습니다. 하지만 요구사항은 A, E, C, D 열처럼 비연속적인 위치에 각각 값을 붙여 넣는 것이므로, 이 방식은 맞지 않습니다. 따라서 한 번에 처리할 수 없고, 열별로 따로 나누어 붙이는 방법을 써야 합니다~

@수메리안 님 와~ 너무너무 감사드립니다. 전문가적인 답변을 이렇게 정성들여 작성해주시니 제가 몸둘바를 모르겠습니다. 큰 가르침을 받았습니다. 제가 지금 막 배열을 공부하는 중인데 행X열 에 대한 개념을 알긴 알겠는데 그것을 가공하는 방식이 상당히 복잡하다는 것을 느낍니다. 제 소스에서는 Union과 Transpose를 사용했지만 선생님께서 첨삭해주신 소스는 이 함수를 쓰지않고 바로 4X4 배열을 1개의 배열로 넣으신 거 같은데... 아~ 제가 아직 배열에 대한 개념이 명확치못해 어떻게 하신 건지 아직은 잘 모르겠습니다. 더욱 정진하여 다음에 가르쳐주신 부분을 꼭 복기하여 이해하도록 하겠습니다. 거듭 감사드립니다. 넙죽 (__)