VBA(excel)의 행을 루프하는 가장 효율적이고 빠른 방법은 무엇입니까?

VBA(excel)의 행을 루프하는 가장 효율적이고 빠른 방법은 무엇입니까?

Excel의 VBA가 가장 빠른 것은 아니지만, 많은 샘플의 행을 루프하는 가장 효율적인 방법(즉, 가장 빠른)이 필요합니다.

현재 가지고 있는 것은 다음과 같습니다.

For Each c In Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).row
    ' do stuff
Next c

'do stuff'에는 행 삽입이 포함되어 있습니다(따라서 범위의 동적 검색을 유지해야 합니다).

아이디어 있나요? (10,000줄 이상)

이미 사용 중인 편집

Application.ScreenUpdating = False
Application.Calculation = xlManual

열 A의 10k 행을 루프하는 경우 행을 배리언트 배열로 덤프한 후 루프합니다.

그런 다음 요소를 새 배열에 추가하고(필요에 따라 행을 추가하면서) Transpose()를 사용하여 어레이를 한 번에 범위로 배치하거나 반복 변수를 사용하여 현재 있는 행을 추적하고 행을 추가할 수 있습니다.

Dim i As Long
Dim varray As Variant

varray = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value

For i = 1 To UBound(varray, 1)
    ' do stuff to varray(i, 1)
Next

다음은 각 셀을 평가한 후 행을 추가하는 방법의 예입니다.이 예에서는 A열에 foo라는 단어가 있는 각 행 뒤에 행을 삽입합니다.A2에서 시작하므로 삽입 중에 변수 i에 "+2"가 추가되는 것은 아닙니다.어레이를 A1로 시작하면 +1이 됩니다.

Sub test()

Dim varray As Variant
Dim i As Long

varray = Range("A2:A10").Value

'must step back or it'll be infinite loop
For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    'do your logic and evaluation here
    If varray(i, 1) = "foo" Then
       'not how to offset the i variable 
       Range("A" & i + 2).EntireRow.Insert
    End If
Next

End Sub

개요 및 권장사항 편집

사용방법for each cell in range구축 자체가 느린 것은 아닙니다.느린 것은 루프에서 Excel에 반복적으로 액세스하는 것입니다(셀 값 읽기 또는 쓰기, 형식, 행 삽입/삭제 등).

무엇이 너무 느리느냐는 당신의 필요에 달려있다.실행하는 데 몇 분 걸리는 Sub는 거의 사용하지 않는 경우에만 사용할 수 있지만, 10초 걸리는 Sub는 자주 실행하는 경우 속도가 너무 느릴 수 있습니다.

몇 가지 일반적인 조언이 있습니다.

1. 처음에는 단순하게 해주세요.결과가 요구 사항에 비해 너무 느릴 경우 최적화
2. 루프 내용의 최적화에 초점을 맞추다
3. 루프가 필요하다고만 생각하지 마세요때때로 다른 방법이 있다.
4. 루프 내에서 셀 값(대량)을 사용해야 할 경우 루프 외부의 배리언트 배열에 로드합니다.
5. 삽입물의 복잡성을 피하는 좋은 방법은 아래에서 위로 범위를 반복하는 것이다.
   (for index = max to min step -1)
6. 그렇게 할 수 없고 '여기저기 행 삽입'이 많지 않은 경우에는 삽입할 때마다 어레이를 새로고침하는 것을 고려해 주십시오.
7. 다른 셀 속성에 액세스해야 하는 경우value
8. 여러 행을 삭제하려면 루프의 다중 영역 범위에 대한 범위 참조를 작성한 후 루프 후에 해당 범위를 한 번에 삭제하는 것이 좋습니다.

예(테스트되지 않음!)

Dim rngToDelete as range
for each rw in rng.rows
    if need to delete rw then

        if rngToDelete is nothing then
            set rngToDelete = rw
        else
            set rngToDelete = Union(rngToDelete, rw)
        end if

    endif
next
rngToDelete.EntireRow.Delete

오리지널 투고

일반적인 통념에 따르면 세포를 통해 루핑하는 것은 나쁘고 변종 어레이를 통해 루핑하는 것은 좋다고 합니다.나도 한동안 이것을 옹호해 왔다.당신의 질문에 저는 생각을 하게 되었고, 몇 가지 간단한 테스트를 통해 놀라운 결과를 얻었습니다.

세트: 셀 내의 : " " "A1A1000000 행1,000,000 행)

테스트 케이스 1: 어레이 루프

Dim v As Variant
Dim n As Long

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    'i = i + 1
    'i = r.Cells(n, 1).Value 'i + 1
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Array Count = " & Format(n, "#,###")

결과:

Array Time = 0.249 sec
Array Count = 1,000,001

테스트 케이스 2: 범위 루프

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

결과:

Range Time = 0.296 sec
Range Count = 1,000,000

즉, 어레이의 루핑은 고속이지만 예상보다 훨씬 적은 19%밖에 되지 않습니다.

테스트 3: 셀 참조를 사용하여 어레이를 루프

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    i = r.Cells(n, 1).Value
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Array Count = " & Format(i, "#,###")

결과:

Array Time = 5.897 sec
Array Count = 1,000,000

테스트 케이스 4: 셀 참조가 있는 루프 범위

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
    i = c.Value
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

결과:

Range Time = 2.356 sec
Range Count = 1,000,000

따라서 단일 셀 참조를 사용하는 이벤트에서는 루프가 크기만큼 느려지고 범위 루프가 2배 빨라집니다.

결론은 루프 내에서 무엇을 하느냐가 가장 중요합니다.속도가 정말 중요하다면 모든 옵션을 테스트해 보세요.

FWIW, Excel 2010 32비트, Win7 64비트에서의 모든 테스트

• ScreenUpdating 꺼짐,
• Calulation 「」,
• Events무효로 합니다.

어떤 이유에선지 각각이 I=1 ~ X보다 훨씬 빠릅니다.그냥 같은 사전을 뒤져봐

---

dDict의 각 Dkey에 대해 1회,

---

및 Dkey = lbound(dDict.keys)에서 ubound(dDict.keys)로 한 번 이동합니다.

=> 같은 구조를 하고 있어도, 큰 차이를 알 수 있습니다.

언급URL : https://stackoverflow.com/questions/8178161/what-is-the-most-efficient-quickest-way-to-loop-through-rows-in-vba-excel