이 글의 목적은 엑셀 VBA에서 For Each 루프가 특정 셀 범위를 인식하지 못하거나 일부만 순회하는 문제를 재현 가능한 원인별로 분류하고, 즉시 적용 가능한 코드 패턴과 점검 체크리스트를 제공하여 실무에서 신속히 문제를 해결하도록 돕는 것이다.
1. 문제의 전형적 증상과 신속 진단 흐름
다음과 같은 현상이 보고되는 경우가 많다.
For Each c In Range("A1:A100")로 작성했으나 루프가 한 번도 실행되지 않거나 일부 셀만 순회한다.SpecialCells로 필터링한 후For Each를 도는 순간1004오류가 발생한다.- 병합된 셀 범위를 대상으로 순환할 때 예기치 않게 같은 셀을 중복 방문하거나 건너뛴다.
- 시트 한정이 빠져 다른 시트의 동일 주소 범위를 순회한다.
Range(...).Value를 대상으로For Each를 돌렸는데 셀 대신 값 배열로 순회가 진행되어 타입 불일치가 난다.
신속 진단은 아래 순서로 수행한다.
- 대상 시트가 명시적으로 한정되어 있는지 확인한다.
Set rng = ...에서rng Is Nothing인지,rng.Areas.Count > 1인지 확인한다.SpecialCells사용 시 결과 유효성 및 오류 처리 유무를 점검한다.- 병합 셀 포함 여부를 확인하고
MergeArea처리 전략을 정한다. - 자동필터 적용 시
xlCellTypeVisible과의 조합을 검증한다. Value배열 순회인지Range객체 순회인지 타입을 명확히 구분한다.
2. 시트 한정 누락으로 인한 범위 오인식
가장 빈번한 원인은 대상 워크시트가 명시되지 않아 ActiveSheet를 기준으로 범위를 해석하는 문제이다. 다음 코드는 위험하다.
Sub BadExample() Dim c As Range For Each c In Range("A1:A10") ' ActiveSheet 기준으로 동작 Debug.Print c.Address Next c End Sub 항상 워크시트를 한정한다.
Sub GoodExample() Dim ws As Worksheet, rng As Range, c As Range Set ws = ThisWorkbook.Worksheets("데이터") Set rng = ws.Range("A1:A10")
Dim sh As Worksheet
For Each c In rng
Debug.Print ws.Name, c.Address(External:=False), c.Value2
Next c
End Sub
주의 : 다른 통합 문서의 시트를 대상으로 할 때는
Workbooks("이름.xlsx").Worksheets("시트")까지 완전 한정해야 한다.3. SpecialCells와 필터 가시 셀 처리
SpecialCells는 결과가 없을 때 1004 오류를 던진다. 안전 패턴은 다음과 같다.
Sub LoopVisible() Dim ws As Worksheet, rng As Range, vis As Range, c As Range Set ws = ActiveSheet Set rng = ws.Range("A1:A100")
On Error Resume Next
Set vis = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If vis Is Nothing Then
Debug.Print "가시 셀 없음"
Exit Sub
End If
' 불연속 영역 대비
Dim area As Range
For Each area In vis.Areas
For Each c In area.Cells
Debug.Print c.Address, c.Value2
Next c
Next area
End Sub
주의 : 자동필터 후 가시 셀만 순회하려면
ws.UsedRange.SpecialCells(xlCellTypeVisible)처럼 과도한 범위 사용을 피하고 필요한 열만 명시한다. 불연속 영역이면 Areas 중첩 루프가 필수이다.4. 병합 셀 포함 범위 순회
병합 셀을 포함하면 For Each는 병합된 영역의 좌상단 셀만 순회 대상으로 보는 것이 일반적이다. 병합 영역 전체를 처리하려면 MergeArea를 사용한다.
Sub LoopMerged() Dim c As Range, area As Range For Each c In Range("A1:A20") If c.MergeCells Then For Each area In c.MergeArea Debug.Print area.Address, area.Value2 Next area Else Debug.Print c.Address, c.Value2 End If Next c End Sub 주의 : 병합 셀은 데이터 처리의 복잡도를 증가시킨다. 가능하면 병합을 제거하고
CenterAcrossSelection 서식을 사용하는 설계를 권장한다.5. Range vs 배열: For Each 대상 타입 혼동
Range("A1:A10").Value는 2차원 Variant 배열을 반환한다. 이때 For Each는 Range가 아니라 값 요소를 순회한다. 타입을 의도에 맞게 구분해야 한다.
Sub LoopValues() Dim arr As Variant, v arr = Range("A1:A10").Value2 ' 1-based 2D 배열 For Each v In arr Debug.Print TypeName(v), v Next v End Sub 셀 객체를 순회하려면 Range를 대상에 둔다.
Sub LoopCells() Dim c As Range For Each c In Range("A1:A10").Cells Debug.Print c.Address, c.Value2 Next c End Sub 주의 : 배열 순회와 셀 순회를 혼용하면
Type mismatch가 발생한다. 특히 Dim c As Range : For Each c In Range("A1:A10").Value는 오류이다.6. 불연속(다중영역) 범위 처리
Union 또는 자동필터 결과는 불연속 범위가 된다. 이때는 Areas를 반드시 순회한다.
Sub LoopDiscontinuous() Dim ws As Worksheet, rng As Range, area As Range, c As Range Set ws = ActiveSheet Set rng = Union(ws.Range("A1:A5"), ws.Range("A10:A12"))
For Each area In rng.Areas
For Each c In area.Cells
Debug.Print area.Address, c.Address, c.Value2
Next c
Next area
End Sub
7. UsedRange, CurrentRegion, Tables의 함정
UsedRange는 과거 사용 흔적 때문에 생각보다 넓거나 좁을 수 있다. 신뢰성이 필요한 루프는 주소를 명시하거나 테이블 객체(ListObject)의 DataBodyRange를 사용한다.
Sub LoopTable() Dim lo As ListObject, c As Range Set lo = ActiveSheet.ListObjects("tblData") If Not lo.DataBodyRange Is Nothing Then For Each c In lo.DataBodyRange.Columns(1).Cells Debug.Print c.Address, c.Value2 Next c End If End Sub CurrentRegion은 빈 행·열이 경계를 만든다. 데이터 중간의 공백으로 인해 범위가 분리되는지 확인한다.
8. SpecialCells(xlCellTypeConstants/Formula) 결과 0건 처리
상수만, 수식만 루프하려고 할 때 결과 0건이면 1004가 난다. 방어 코드를 사용한다.
Sub LoopConstantsSafe() Dim rng As Range, tgt As Range, c As Range Set rng = Range("B2:B1000") On Error Resume Next Set tgt = rng.SpecialCells(xlCellTypeConstants) On Error GoTo 0
If tgt Is Nothing Then Exit Sub
For Each c In tgt.Cells
Debug.Print c.Address, c.Value2
Next c
End Sub
9. 수식 평가(Evaluate)와 배열 반환
Evaluate는 배열을 반환할 수 있다. For Each 대상이 배열인지 객체인지 구분한다.
Sub LoopEvaluateArray() Dim arr As Variant, v arr = Evaluate("ROW(1:10)") ' 1~10 배열 For Each v In arr Debug.Print v Next v End Sub 반면 Evaluate로 Range를 얻고자 한다면 괄호를 적절히 사용한다.
Sub LoopEvaluateRange() Dim rng As Range, c As Range Set rng = Evaluate("A1:A10") For Each c In rng.Cells Debug.Print c.Address, c.Value2 Next c End Sub 주의 :
Evaluate는 통합 문서·시트 컨텍스트에 민감하다. 가독성과 유지보수를 위해 가능하면 Worksheet.Range 참조를 선호한다.10. 변형된 셀 상태: 숨김, 보호, 데이터 유효성
숨김 또는 보호 상태는 루프 자체를 막지 않지만, SpecialCells(xlCellTypeVisible) 조합과 충돌하거나 쓰기 시 예외를 유발할 수 있다. 쓰기 전 검사 패턴을 사용한다.
Sub WriteIfUnlocked() Dim c As Range For Each c In Range("C2:C100").Cells If Not c.Locked Then c.Value2 = "OK" End If Next c End Sub 11. Nothing, Count, Rows/Columns 검증
루프 전 간단한 유효성 검증으로 대부분의 낭비를 줄일 수 있다.
Function IsValidRange(rng As Range) As Boolean IsValidRange = Not (rng Is Nothing) _ And rng.Cells.CountLarge > 0 _ And rng.Parent.Parent Is ThisWorkbook End Function 12. 성능 최적화: 대량 처리 시 안전한 패턴
셀 단위 루프는 느리다. 범위를 배열로 일괄 읽고, 처리 후 다시 쓰는 전략이 권장된다.
Sub BulkProcess() Dim ws As Worksheet, src As Range, arr As Variant Dim i As Long, n As Long
Set ws = ActiveSheet
Set src = ws.Range("A2:A100000")
arr = src.Value2 ' 2D 배열
n = UBound(arr, 1)
For i = 1 To n
If Len(arr(i, 1)) = 0 Then
arr(i, 1) = "N/A"
End If
Next i
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
src.Value2 = arr
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
주의 : 성능 최적화를 하더라도 오류 처리와 상태 복구는 반드시
On Error와 Finally 패턴으로 보장한다.13. 오류 코드별 원인 매핑
| 오류/증상 | 주요 원인 | 해결 방법 |
|---|---|---|
| 루프 미실행 | 범위가 비었거나 시트 한정 누락으로 잘못된 시트 참조 | 워크시트 완전 한정, rng Is Nothing·CountLarge 확인 |
| 1004 with SpecialCells | 결과 0건 | On Error Resume Next로 결과 검증 후 Is Nothing 체크 |
| Type mismatch | 배열 요소에 Range 변수 대입 | 값 배열은 Variant로 순회, 셀은 Range로 순회 |
| 일부 셀 건너뜀 | 병합 셀, 불연속 영역 | MergeArea 또는 Areas 중첩 루프 |
| 다른 시트 순회 | ActiveSheet 의존 | Workbooks·Worksheets 완전 한정 |
14. 견고한 루프 템플릿
다양한 함정을 회피하도록 구성한 범용 템플릿이다.
Sub RobustForEach(ByVal wbName As String, ByVal wsName As String, ByVal addr As String) Dim wb As Workbook, ws As Worksheet Dim src As Range, vis As Range Dim area As Range, c As Range
On Error GoTo FAIL
Set wb = Workbooks(wbName)
Set ws = wb.Worksheets(wsName)
Set src = ws.Range(addr)
If src Is Nothing Then GoTo DONE
If src.Cells.CountLarge = 0 Then GoTo DONE
' 필터 가시 셀만 순회하려면 주석 해제
'On Error Resume Next
'Set vis = src.SpecialCells(xlCellTypeVisible)
'On Error GoTo 0
'If vis Is Nothing Then GoTo DONE
If vis Is Nothing Then Set vis = src
For Each area In vis.Areas
For Each c In area.Cells
If c.MergeCells Then
Dim ma As Range
For Each ma In c.MergeArea.Cells
ProcessCell ma
Next ma
Else
ProcessCell c
End If
Next c
Next area
DONE:
Exit Sub
FAIL:
Debug.Print "오류:", Err.Number, Err.Description
Resume DONE
End Sub
Private Sub ProcessCell(ByVal c As Range)
' 업무 규칙 처리
Debug.Print c.Address(External:=False), c.Value2
End Sub
15. 테스트 데이터 생성과 재현성 확보
문제를 재현하고 회귀 테스트를 만들기 위한 더미 데이터 생성 절차이다.
Sub MakeDummy() Dim ws As Worksheet Set ws = ActiveSheet ws.Cells.Clear
ws.Range("A1").Value2 = "머리글"
ws.Range("A2:A20").Formula = "=ROW()"
ws.Range("A5:A6").Merge
ws.Range("A12").EntireRow.Hidden = True
ws.Range("B1").Value2 = "상수"
ws.Range("B2:B20").Value2 = "X"
ws.Range("C1").Value2 = "가변"
ws.Range("C2:C20").Formula = "=RAND()"
ws.Range("A1:C1").EntireColumn.AutoFilter
End Sub
16. 패턴별 체크리스트
- 항상
Workbooks·Worksheets·Range를 완전 한정한다. SpecialCells는 결과 0건 예외를 가정하고 코드를 작성한다.- 불연속 범위는
Areas를 먼저 순회한다. - 병합 셀은
MergeArea와 동작 차이를 이해하고 필요 시 병합을 제거한다. - 배열 순회와 셀 순회를 혼동하지 않는다.
- 대량 처리는 배열 일괄 변환 후 기록한다.
- 필터·숨김과 보호 상태의 영향을 구분한다.
17. 자주 쓰는 안전 유틸리티
Function VisibleCellsOrNothing(ByVal rng As Range) As Range On Error Resume Next Set VisibleCellsOrNothing = rng.SpecialCells(xlCellTypeVisible) On Error GoTo 0 End Function
Function FirstDataColumn(ByVal lo As ListObject) As Range
If lo Is Nothing Then Exit Function
If lo.DataBodyRange Is Nothing Then Exit Function
Set FirstDataColumn = lo.DataBodyRange.Columns(1)
End Function
18. 실제 업무 시나리오 예제
조건부로 비어 있지 않은 가시 셀만 편집하는 시나리오이다.
Sub Scenario() Dim ws As Worksheet, rng As Range, tgt As Range Dim area As Range, c As Range
Set ws = ThisWorkbook.Worksheets("입력")
Set rng = ws.Range("D2:D10000")
Set tgt = VisibleCellsOrNothing(rng)
If tgt Is Nothing Then Exit Sub
For Each area In tgt.Areas
For Each c In area.Cells
If Len(c.Value2) > 0 And Not c.Locked Then
c.Offset(0, 1).Value2 = "검증완료"
End If
Next c
Next area
End Sub
19. 디버깅 팁
Debug.Print rng.Address, rng.Areas.Count, rng.Cells.CountLarge로 대상 구조를 즉시 확인한다.Stop또는 중단점을 사용하고Locals·Watch창에서Is Nothing을 확인한다.- 필터 상태에서 예외가 나면 필터 해제 후 동일 코드로 재현해 원인을 단순화한다.
20. 마이그레이션 고려사항(32/64비트, 환경 차이)
For Each 자체는 32/64비트 차이가 없다. 다만 참조 설정과 외부 DLL 호출이 있는 프로젝트는 별도로 정비해야 한다. 본 문서의 패턴은 비트수와 무관하게 동일하게 적용된다.
FAQ
Q1. For Each 루프가 느리다. 대안이 있는가?
값 처리 위주라면 범위를 배열로 읽은 후 인덱스 기반 For 루프를 사용하고 마지막에 한 번에 써 넣는 방식이 가장 빠르다.
Q2. 병합 셀이 꼭 필요하다. 중복 방문을 피하는 간단한 방법은?
If c.Address = c.MergeArea.Cells(1,1).Address Then 조건으로 병합 영역의 대표 셀만 처리한다.
Q3. 자동필터로 가시 셀만 도는데 1004가 난다.
결과 0건이다. On Error Resume Next로 SpecialCells 호출을 감싼 뒤 Is Nothing을 검사한다.
Q4. 다른 통합 문서를 대상으로 루프가 돈다.
워크북·시트 한정을 명시하지 않았다. Workbooks("파일").Worksheets("시트").Range(...)로 완전 한정한다.
Q5. 값 배열을 루프하는데 타입 불일치가 난다.
Dim v 또는 Dim v As Variant로 선언하고 For Each v In arr로 순회한다. Range 변수로는 값 요소를 받을 수 없다.