복사할 범위와 붙여 넣을 범위가 모두 필터링된 경우에 복사해서 붙여 넣기를 다뤄보겠습니다.
1. 작업 내용
사례로서 적합한 데이터는 아니지만
첫 번째 시트에는 가락2동으로 필터 된 데이터가 있고,
두 번째 시트는 가락1동으로 필터 된 데이터가 있어서
첫 번째 시트의 내용을 복사해서
두 번째 시트에 붙여 넣으면
9행이 아니라 숨겨진 행인 7,8행에 붙여 넣기가 됩니다.
따라서, 매크로를 작성해서 처리해야 합니다.
2. 논리
복사할 범위도 필터링되어 있기 때문에
for c in PasteRange
for d in CopyRange
라고 CopyRange에서 화면에 보이는 셀(범위)만 하나씩 실행하는 것을 생각할 수 있는데,
이렇게 하면 이중 반복문이므로 붙여 넣을 범위의 행수 * 복사할 범위의 행수만큼 반복하게 되므로 원하는 바가 아닙니다.
왜냐하면 복사할 범위의 행수만큼만 실행돼야 하기 때문입니다.
따라서, 복사할 범위를 한 줄씩 배열에 넣은 후 배열을 하나씩 불러와 처리해야 합니다.
3. 매크로 작성
Option Explicit
Option Base 1
Dim CopyRange As Range, PasteRange As Range
Dim Row_limit As Integer, Column_Limit As Integer
Dim i As Integer
Dim c As Range
Sub FilteredRange_Copy6()
On Error Resume Next
Dim CopyRow() As Range
Sheets(1).Select
Range("a2").Select
If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락2*"
Sheets(2).Select
Range("a2").Select
If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*"
Sheets(1).Select
Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8)
If CopyRange Is Nothing Then Exit Sub 'End
Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible)
Column_Limit = CopyRange.Columns.Count
i = 1
For Each c In CopyRange
If c.Column = 1 Then
ReDim Preserve CopyRow(i)
Set CopyRow(i) = c.Resize(1, Column_Limit) 'Range(c, c.Offset(0, Column_Limit - 1))
i = i + 1
End If
Next
Row_limit = i - 1
Sheets(2).Select
Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", _
Type:=8, Default:=Range("a2").Address(0, 0))
If PasteRange Is Nothing Then End
Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible)
i = 1
For Each c In PasteRange
' 인덱스 값 i가 CopyRange 배열의 1차원 인덱스 값 상한보다 작거나 같다면
If i <= Row_limit Then
Sheets(1).Select
CopyRow(i).Copy
Sheets(2).Select
c.Select
ActiveSheet.Paste
Else
' ActiveSheet.CutCopyMode = False
Range("a2").Select
End
End If
i = i + 1
Next
End Sub
4. 매크로 설명
(1) 프로시저 명 정의하기
Sub FilteredRange_Copy6() : sub 프로시저명을 정의합니다.
(2) 인덱스 시작 값을 1로 설정하기
Option Base 1 : 배열의 인덱스가 0부터 시작하는데 1부터 시작하도록 설정하는 것입니다.
(3) 공용(Public) 변수 선언
Sub 프로시저 위에서 선언해서 여러 프로시저에 공통적으로 적용되도록 하는 공용 변수 선언입니다.
| Dim CopyRange As Range, PasteRange As Range Dim Row_limit As Integer, Column_Limit As Integer Dim i As Integer Dim c As Range, d As Range |
변수의 성격에 따라 범위, 정수 형식으로 선언합니다.
(4) 에러 시 처리 방법 및 CopyRow 배열 선언
On Error Resume Next : 에러가 발생하더라도 다음 줄을 실행하도록 합니다. 취소 버튼을 누른 경우 처리를 위한 것입니다. 아무 셀도 선택하지 않고 확인 버튼을 누르면 수식 에러가 발생합니다.
Dim CopyRow() As Range : CopyRow 배열을 크기를 지정하지 않고 범위 형식으로 선언합니다. 왜냐하면 배열의 크기가 가변적이기 때문에 나중에 ReDim으로 다시 정의하려고 하는 것입니다.
(5) 필터링 되지 않은 경우 필터링 걸기
| Sheets(1).Select Range("a2").Select If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락2*" Sheets(2).Select Range("a2").Select If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*" |
Sheets(1).Select : 첫 번째 시트를 선택합니다.
Range("a2").Select : A2셀을 선택합니다.
If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락2*" : 두 번째 필드인 동명이 '가락2'로 시작하는 데이터로 필터링합니다.
Sheets(1).Range("a2").Select로 수정한 다음 시트 2에서 매크로를 실행하면 필터링이 안됩니다.
Sheets(2).Select 부터는 위 설명을 참고하면 됩니다.
(6) 복사할 범위 선택 및 취소 버튼시 종료
| Sheets(1).Select Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8) If CopyRange Is Nothing Then Exit Sub |
Sheets(1).Select : 다시 첫 번째 시트를 선택한 다음
Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8) : 복사할 범위를 입력받기 위한 상태가 됩니다.
If CopyRange Is Nothing Then Exit Sub : CopyRange가 없다면, 다시 말해 범위 선택이 안 된 경우 Sub 프로시저를 종료합니다. Exit Sub 대신 End라고 해도 됩니다.
(7) 복사할 범위를 화면에 보이는 셀로만 변경하고, 열의 개수를 Column_Limit에 대입
| Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible) Column_Limit = CopyRange.Columns.Count |
Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible) : 복사할 범위에서 화면에 보이는 셀만 다시 CopyRange범위로 지정합니다.
Column_Limit 왼쪽 기둥을 클릭해서 중단점을 설정한 후 매크로를 실행하고
범위를 A7:K23으로 선택하고, 확인 버튼을 누르면
Column_Limit줄에서 실행을 멈추는데,
직접 실행창에 ?copyrange.Address 라고 입력하고 엔터키를 누르면
화면에 보이는 셀만 컴마로 구분해서 표시됩니다.
화면 위 표준도구 모음에서 네모 아이콘을 눌러 실행을 멈춥니다.
Column_Limit = CopyRange.Columns.Count : CopyRange의 열 수를 Column_Limit에 대입합니다.
직접 실행창에서 CopyRange.Columns.Count을 확인해 보면 11인데, A열부터 K열까지의 열의 개수입니다.
(8) 복사할 범위의 행 수 구하기
| i = 1 For Each c In CopyRange If c.Column = 1 Then ReDim Preserve CopyRow(i) Set CopyRow(i) = c.Resize(1, Column_Limit) 'Range(c, c.Offset(0, Column_Limit - 1)) i = i + 1 End If Next ReDim Preserve CopyRow(i - 1) Row_limit = i - 1 |
CopyRow 배열에 복사할 범위의 보이는 셀(행)을 저장하는 작업을 하는 구문입니다.
i = 1 : 배열의 경우 인덱스가 0부터 시작하는 1부터 시작하는 것으로 했으므로 i값을 1로 선언했습니다.
For Each c In CopyRange : 화면에 보이는 셀로 구성된 CopyRange의 각 셀을 이동하면서 반복합니다.
If c.Column = 1 Then : CopyRange의 열이 1인 경우, 다시 말해 다른 열은 처리하지 않고, 첫 번째 열인 경우만 처리하기 위한 조건문입니다.
ReDim Preserve CopyRow(i) : 배열의 크기를 저장된 값을 보존하면서 i의 크기로 변경합니다.
Set CopyRow(i) = c.Resize(1, Column_Limit) 'Range(c, c.Offset(0, Column_Limit - 1)) : CopyRow(i) 범위 변수에 처리 중인 셀, c를 기준으로 1행, 열의 개수 크기로 범위를 재설정합니다.
c.Resize(1, Column_Limit)는 c가 A7셀일 경우 A7:K7이 됩니다.
Range(c, c.Offset(0, Column_Limit - 1)) : Offset함수를 이용하면 현재 셀에서 현재 셀을 기준으로 행은 그대로, 열은 Column_Limit보다 1 작은 값을 범위로 지정합니다. Resize는 크기를 지정하는데 비해서 Offset은 상대적인 위치를 지정하기 때문에 1씩 작습니다.
i = i + 1 : i값을 1씩 증가시킵니다.
End If : If문을 닫고,
Next : For문을 닫습니다.
Row_limit = i - 1 : 행수를 i - 1로 지정합니다. i값이 커져 있기 때문에 -1을 한 것입니다.
지역 창에서 CopyRow의 배열을 확인해 보면 7개입니다.
Row_limit도 해당 줄에 중단점을 설정한 후 매크로를 실행하고 F8키를 눌러 한 단계 실행한 후, 직접 실행창에서 Row_limit의 값을 확인해 보면 7입니다.
CopyRange가 $A$7:$K$7,$A$10:$K$10,$A$13:$K$13,$A$16:$K$16,$A$19:$K$19,$A$22:$K$23인 상태에서
아래 3개의 값을 구해보면 Colums.Count만 숫자가 맞고, Areas.Count와 Rows.Count로는 행의 개수 7을 구하지 못함을 알 수 있습니다.
| Areas.Count | Rows.Count | Columns.Count |
| ?copyrange.Areas.Count 6 선택된 범위의 개수로 컴마를 기준으로 개수를 셉니다. $A$22:$K$23가 1줄씩 범위가 지정되었다면 7이 됐을텐데 2줄이 하나의 범위가 돼서 6이 반환됐습니다. |
?copyrange.Rows.Count 1 Rows.Count는 맨 앞의 범위인 $A$7:$K$7만을 가지고 행 수를 계산해서 1이 반환된 것입니다. |
?copyrange.columns.Count 11 Columns.Count도 첫번째 범위인 $A$7:$K$7를 가지고 열의 개수를 계산하지만, 열의 개수가 11이기 때문에 11이 구해집니다. |
따라서, 위와 같이 For ~ Next문을 이용해 Row_limit, 행의 개수를 구한 것입니다.
(9) 붙여 넣을 첫 번째 셀 선택 및 PasteRange를 화면에 보이는 셀만으로 변경
| Sheets(2).Select Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", _ Type:=8, Default:=Range("a2").Address(0, 0)) If PasteRange Is Nothing Then End Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible) |
Sheets(2).Select
Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", _
Type:=8, Default:=Range("a2").Address(0, 0))
If PasteRange Is Nothing Then End
이 부분은 복사할 범위를 입력받는 부분과 같은데, 두 번째 시트에서 작업하는 것만 다릅니다.
Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible) : 붙여 넣을 범위를 선택된 셀을 기준으로 연속된 맨 아래행까지 범위를 잡은 후 화면에 보이는 셀만으로 범위를 재설정합니다.
(10) CopyRange를 한 줄씩 복사해서 PasteRange의 셀을 이동하면서 붙여 넣기
| i = 1 For Each c In PasteRange ' 인덱스 값 i가 CopyRange 배열의 1차원 인덱스 값 상한보다 작거나 같다면 If i <= Row_limit Then Sheets(1).Select CopyRow(i).Copy Sheets(2).Select c.Select ActiveSheet.Paste Else ActiveSheet.CutCopyMode = False Range("a2").Select End End If i = i + 1 Next |
i = 1 : i값을 1로 초기화합니다.
For Each c In PasteRange : PasteRange의 셀을 하나씩 이동하면서 For ~ Next 안의 구문을 반복 처리합니다.
If i <= Row_limit Then : i 값이 Row_limit(복사할 범위의 행 수) 보다 작거나 같은 경우만 처리합니다.
Sheets(1).Select : 첫 번째 시트로 이동합니다.
CopyRow(i).Copy : CopyRange의 i번째 범위를 복사합니다.
Sheets(2).Select : 두 번째 시트로 이동합니다.
c.Select : PasteRange의 처리 셀로 이동합니다.
ActiveSheet.Paste : 처리 셀을 기준으로 붙여 넣습니다.
Else : i값이 Row_limit(복사할 범위의 행 수) 보다 큰 경우
ActiveSheet.CutCopyMode = False : 복사 모드를 False, 다시 말해 복사하기 위해 선택한 부분의 선택을 제거합니다.
Range("a2").Select : A2셀로 이동합니다.
End : Sub 프로시저를 끝냅니다.
i = i + 1 : CopyRange의 인덱스인 i를 1씩 증가시키면서 반복문을 처리합니다.
Next : For문을 닫습니다.
(11) Sub 프로시저 닫기
End Sub : Sub 프로시저를 닫습니다.
5. 매크로 실행
개발 도구 탭에서 매크로를 누른 후 FilteredRange_Copy6을 선택하고 실행 버튼을 누르면
복사할 범위를 선택하라고 하는데, 아래와 같이 지정하고 확인 버튼을 누릅니다.
이번에는 붙여 넣을 첫 번째 셀을 지정하라고 하는데 기본 값으로 A2셀이 제시되므로 확인 버튼을 누릅니다.
숨겨진 셀(행)은 제외하고, 화면에 보이는 셀, 2행부터 6행까지와 그다음에 9행과 12행에 복사한 값이 붙여 넣어집니다.
'EXCEL - VBA' 카테고리의 다른 글
| 일정한 간격으로 된 값 합계 구하기 - 사용자 정의 함수 (2) | 2023.03.20 |
|---|---|
| 엑셀에서 전체 범위 선택하기 - VBA (2) | 2023.03.15 |
| Range.AutoFilter Method(2) - Field, Criteria, Operator, SubField, VisibleDropDown (2) | 2023.03.12 |
| Range.AutoFilter Method(1) - expression.AutoFilter (2) | 2023.03.11 |
| Range.AutoFilter Method(7) - 필터된 영역에 붙여넣기(2-2) (0) | 2023.03.10 |