Range.AutoFilter Method(6) - 필터된 영역에 붙여넣기(2-1)

이번에는 한 열이 아니라 25행에서 30행에 있는 A부터 K까지 열을 복사해서 붙여 넣기를 해보겠습니다.

서울시 지역 시간별 수질 현황6.xlsx

0.01MB

 

 

 

1. Rows() 함수 이용

가. 모듈 삽입

줄 전체를 복사하는 것은 Rows 함수를 이용하여 쉽게 구현할 수 있습니다.

 

먼저 개발도구 - Visual Basic을 클릭해서

 

Visua Basic Editor로 들어간 다음 '서울시 지역 ....xlsx 파일이 선택된 상태에서 삽입 - 모듈을 누르거나 사용자 정의 폼 등 삽입을 위한 콤보 상자 버튼을 누른 후 

 

모듈을 삽입합니다.

 

나. 매크로 작성

Range.AutoFilter Method(6) - 필터된 영역에 붙여넣기(1)의 FilteredRange_Copy3 sub 프로시저를 참고해서 만들었는데, CopyRange로 복사할 행의 한 열만 선택하도록 했고,

Sub FilteredRange_Copy4()
    Range("a2").Select
    If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*"
    
    Set CopyRange = Application.InputBox("복사할 행의 한 열을 선택하세요.", Type:=8)
    Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", Type:=8)
    
    Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible)

    Dim limit As Integer
    limit = UBound(CopyRange())
    
    i = 1
    For Each c In PasteRange
'        인덱스 값 i가 CopyRange배열의 인덱스 값 상한보다 작거나 같다면
        If i <= limit Then
            Rows(CopyRange(i).Row).Copy Destination:=c
        Else
            End
        End If
        i = i + 1
    Next
End Sub

 

아래에서 Rows(CopyRange(i).Row).Copy Destination:=c 부분만 다릅니다.

i = 1     For Each c In PasteRange '        인덱스 값 i가 CopyRange배열의 인덱스 값 상한보다 작거나 같다면         If i <= limit Then             Rows(CopyRange(i).Row).Copy Destination:=c         Else             End         End If         i = i + 1     Next

 

(행을 선택하는 방법)

VBA에서 사용하는 Rows함수는 워크시트에서 사용하는 Rows 함수와 다릅니다.

 

워크시트에서는 =rows(c1:e4) 이라고 입력하면 1행부터 4행까지이므로 행들의 수 4가 반환되는데

 

VBA에서는 행에 해당하는 숫자 한 개 또는 시작 행부터 끝 행까지를 콜론(:)으로 연결하여 인수로 받으며, 그러면 해당하는 행 전체가 선택됩니다.

 

(1행 선택)

예를 들어 직접 실행창에 rows(3).select라고 입력하고 엔터키를 누르면

 

3행이 선택되며,

행 하나가 선택된 화면

 

(2개 이상 연속된 행 선택)

rows("2:4").select라고 입력하고 엔터키를 치면 2행부터 4행까지 선택됩니다.

연속된 행이 선택된 화면

둘의 차이점은 하나일 때는 숫자로 입력하고 여러 행을 선택할 때는 큰 따옴표 안에 숫자를 콜론으로 연결하여 입력한다는 것입니다.

 

(떨어진 행 선택)

떨어진 행을 선택할 때는 rows함수가 아니라 Range 함수를 사용합니다.

range("2:4,6:7").select라고 입력하고 엔터키를 누르면

 

2행부터 4행까지와 6행부터 7행까지 숨겨진 행도 선택됩니다.

떨어진 행이 선택된 화면

 

Rows(CopyRange(i).Row).Copy Destination:=c에서

Rows안의 CopyRange(i).Row는 i번째 인덱스에 해당하는 복사할 범위의 행 수를 반환합니다.

예를 들어 CopyRange(1).Row는 복사할 범위의 첫 번째 행인 25가 반환됩니다.

따라서, Rows(CopyRange(i).Row)는 25행이 됩니다.

 

(매크로 실행)

Rows(CopyRange(i).Row).Copy Destination:=c 줄에 중단점을 설정하고,

i와 CopyRange(i).Row를 조사식에 추가한 후 매크로를 실행하면

 

"복사할 행의 한 열을 선택"하라고 하므로 D25셀부터 D30셀까지 한 열을 선택하고 확인 버튼을 누릅니다.

 

그러면, "붙여넣을 첫 번째 셀을 선택"하라고 하므로 A2셀을 클릭하고 확인 버튼을 누릅니다.

 

그러면 중단점에서 실행을 멈추는데, 아래 조사식 창을 보면 i값은 1이고, CopyRange(i).Row는 25입니다.

중단점에서 실행을 멈추고, 조사식 창에 수식에 대한 값이 표시됨

 

Visual Basic Editor의 표준 도구 모음에서 삼각형 실행 버튼을 누른 후 엑셀 모양의 아이콘은 눌러 엑셀로 돌아가면

 

25행이 2행에 붙여 넣어졌습니다.

 

이제 개발 도구 - Visual Basic 명령을 눌러 Visual Basic Editor로 들어간 다음 중단점을 클릭해서 중단점을 해제하고, 삼각형 실행 아이콘을 누르면 매크로 실행이 모두 완료됩니다.

중단점을 클릭해서 해제하고, 실행 아이콘 클릭

 

엑셀로 돌아가서 확인해 보면 25행부터 30행의 내용이 2행부터 9행에 붙여졌으며, 

화면에 보이는 셀에만 붙여 넣어진 화면

 

필터 - 숨기기 메뉴를 눌러 필터링을 해제하면 숨겨진 행 7행과 8행에는 복사가 안된 것을 알 수 있습니다.

숨겨진 행에는 붙여 넣기가 안됐음

 

2. 복사할 범위 전체를 선택한 경우의 처리

이번에는 복사할 범위로 복사할 범위 전체를 지정하는 것을 처리해 보겠습니다.

 

가. 논리

복사할 범위로 복사할 범위 전체를 지정하면 CopyRange()는 복사할 범위의 셀들을 왼쪽에서 오른쪽으로, 그리고, 오른쪽 끝에 도달하면 아래 행으로 이동하므로 오른쪽으로 이동할 때는 처리하지 말고 아래로 이동할 때만 처리해야 합니다.

 

또한 CopyRange를 2차원으로 보면 가로가 11, 세로가 6인 2차원 배열이며, CopyRange(row_index, column_index)로 표현할 수 있습니다.

 

나. CopyRange, 복사할 범위의 주소 출력

 

(1) 매크로 작성

2차원을 기준으로 CopyRange의 주소를 출력하는 매크로를 만들어보면 아래와 같습니다.

 

Dim CopyRange As Range, PasteRange As Range
Dim c As Range
Dim i As Integer, j As Integer
Dim row_limit As Integer, Column_limit As Integer

Sub print_copyrange()
    Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8)
    row_limit = UBound(CopyRange(), 1)
    Column_limit = UBound(CopyRange(), 2)
    
    Dim print_cells As String
    
    For i = 1 To row_limit
        For j = 1 To Column_limit
            If i = 1 And j = 1 Then
                print_cells = CopyRange(i, j).Address(0, 0)
            ElseIf j = Column_limit Then
                print_cells = print_cells & "," & CopyRange(i, j).Address(0, 0) & vbCrLf
            Else
                If j Mod Column_limit = 1 Then
                    print_cells = print_cells & CopyRange(i, j).Address(0, 0)
                Else
                    print_cells = print_cells & "," & CopyRange(i, j).Address(0, 0)
                End If
            End If
        Next
    Next
    Debug.Print print_cells
End Sub

 

(2) 매크로 설명

 

Dim j As Integer : for 반복문을 이중으로 써야 해서 j변수를 정식으로 만들었습니다.
Dim row_limit As Integer, Column_limit As Integer : 2차원 배열이기 때문에 행의 수와 열의 수를 구하기 위한 변수 row_limit와 column_limit를 정수 형식으로 선언했습니다.

 

row_limit = UBound(CopyRange(), 1) : Ubound함수를 1차원으로 CopyRange배열의 값을 구하면 행 수(6)입니다.
Column_limit = UBound(CopyRange(), 2) : Ubound함수를 2차원으로 CopyRange배열의 값을 구하면 열 수(11)입니다.

 

Dim print_cells As String : print_copyrange 서브 프로시저에서 지역 변수 print_cells를 문자열 형식으로 선언했습니다.

 

For i = 1 To row_limit : i값이 1부터 row_limit, 행 수까지 변하는 동안
    For j = 1 To Column_limit : j값이 1부터 column_limit, 열 수까지 변하는 동안 반복합니다. 이중 반복문입니다.

 

If i = 1 And j = 1 Then : i가 1이고, j가 1인 경우, 다시 말해 복사할 범위의 첫 셀인 경우는 print_cells에 아무것도 없으므로 
    print_cells = CopyRange(i, j).Address(0, 0) : CopyRange(i, j)의 주소를 상대참조형식으로 print_cells에 대입합니다.

 

ElseIf j = Column_limit Then : j값이 열의 개수와 동일하다면, 다시 말해 오른쪽 마지막셀에 도달하면 
    print_cells = print_cells & "," & CopyRange(i, j).Address(0, 0) & vbCrLf : print_cells에 컴마와 엔터 값을 추가합니다.

 

Else : j값이 열의 개수가 아니라면, 다시 말해 오른쪽 끝 셀이 아니라면
    If j Mod Column_limit = 1 Then : j를 열의 개수로 나눈 나머지가 1인 경우, 다시 말해 첫 번째 열인 경우
        print_cells = print_cells & CopyRange(i, j).Address(0, 0) : print_cells에 컴마 없이 CopyRange(i, j)의 주소를 상대참조형식으로 추가합니다.
    Else : j값이 열의 개수도 아니고, 1도 아닌 경우
        print_cells = print_cells & "," & CopyRange(i, j).Address(0, 0) : print_cells에 컴마와 CopyRange(i, j)의 주소를 상대참조형식으로 추가합니다.
    End If
End If

 

        Next : 두 번째 for문을 종료합니다.
    Next : 첫 번째 for문을 종료합니다.
    Debug.Print print_cells : print_cells를 직접 실행창 화면에 출력합니다.

 

 

(3) 매크로 실행

 

개발 도구 - 매크로를 누른 후 print_copyrange 매크로를 선택하고 실행버튼을 누르면

 

복사할 범위로 마우스를 끌어서 A25셀부터 K30셀까지 선택한 후 확인 버튼을 누르면

 

A25셀부터 K30셀까지 주소가 행을 바꿔가면서 출력됩니다.

서울시 지역 시간별 수질 현황(완성6).xlsm

0.02MB